Results 1 to 3 of 3

VBA UserForm MsgBox Code Amendment

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-07-2018
    Location
    Newcastle Upon Tyne, England
    MS-Off Ver
    365
    Posts
    143

    VBA UserForm MsgBox Code Amendment

    I currently have MsgBoxes that appear on screen when CommandButtons are clicked and then automatically disappear after 2 seconds.


    1. 'Update' CommandButton - the MsgBox appears even if no updates have actually been made which is not what I want

    2. 'Delete' CommandButton - the MsgBox only appears when an invoice is selected from the ListBox which is what I want.


    So my question is:


    How do I get the 'Update' CommandButton MsgBox to only appear when an invoice is selected from the ListBox and THEN then 'Update' CommandButton is clicked?


    Codes:

    'Delete' CommandButton which is working perfectly!

    Private Sub DeleteButton_Click()
    
        'Delete Invoice
    
        Dim sn
        With Sheets("MasterData")
            If InvoiceList.ListIndex = -1 Then Exit Sub
                .Cells(InvoiceList.ListIndex + 2, 1).EntireRow.Delete
                sn = .Cells(1).CurrentRegion.Offset(1).Value
        End With
        With InvoiceList
            .ColumnCount = UBound(sn, 2)
            .List = sn
        End With
        With CreateObject("WScript.Shell")
            Select Case .Popup("Your invoice has been deleted.", _
                2, "Deleted", 64)
                Case 1, -1
                    Exit Sub
            End Select
        End With
    
    End Sub
    'Update' Button which is working, but I would prefer it to only pop up when there is data displayed in the fields for updates to have actually been made

    Private Sub UpdateButton_Click()
    
        Dim mydate
        If DateRaised = "" Then
            mydate = vbNullString
        Else
            mydate = CDate(DateRaised)
        End If
        With Sheets("MasterData")
            .Cells(InvoiceList.ListIndex + 2, 1).Value = mydate
            .Cells(InvoiceList.ListIndex + 2, 2).Value = InvoiceNo.Text
            .Cells(InvoiceList.ListIndex + 2, 3).Value = PO.Text
            .Cells(InvoiceList.ListIndex + 2, 4).Value = ContactName.Text
            .Cells(InvoiceList.ListIndex + 2, 5).Value = JobTitle.Text
            .Cells(InvoiceList.ListIndex + 2, 6).Value = OrganisationName.Text
            .Cells(InvoiceList.ListIndex + 2, 7).Value = ContactNo.Text
            .Cells(InvoiceList.ListIndex + 2, 8).Value = AddressLine1.Text
            .Cells(InvoiceList.ListIndex + 2, 9).Value = AddressLine2.Text
            .Cells(InvoiceList.ListIndex + 2, 10).Value = AddressLine3.Text
            .Cells(InvoiceList.ListIndex + 2, 11).Value = AddressLine4.Text
            .Cells(InvoiceList.ListIndex + 2, 12).Value = Postcode.Text
            .Cells(InvoiceList.ListIndex + 2, 13).Value = Description.Text
            .Cells(InvoiceList.ListIndex + 2, 14).Value = ProductServiceLine1.Text
            .Cells(InvoiceList.ListIndex + 2, 15).Value = AmountLine1.Text
            .Cells(InvoiceList.ListIndex + 2, 16).Value = ProductServiceLine2.Text
            .Cells(InvoiceList.ListIndex + 2, 17).Value = AmountLine2.Text
            .Cells(InvoiceList.ListIndex + 2, 18).Value = ProductServiceLine3.Text
            .Cells(InvoiceList.ListIndex + 2, 19).Value = AmountLine3.Text
            .Cells(InvoiceList.ListIndex + 2, 20).Value = ProductServiceLine4.Text
            .Cells(InvoiceList.ListIndex + 2, 21).Value = AmountLine4.Text
            .Cells(InvoiceList.ListIndex + 2, 22).Value = Status.Text
        End With
        With CreateObject("WScript.Shell")
            Select Case .Popup("Your updates have been saved.", _
                2, "Saved", 64)
                Case 1, -1
                    Exit Sub
            End Select
        End With
        
    End Sub

    Thanks as always!
    Last edited by davo3286; 07-15-2018 at 08:52 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Code in userform to bring msgbox if multiple if statements are true
    By ExcelFailure in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-08-2017, 07:28 AM
  2. Userform code to bring up msgbox if two checkboxes are null
    By ExcelFailure in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-06-2017, 01:08 PM
  3. VB code amendment help.
    By sem in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 04-26-2012, 07:23 AM
  4. UserForm or MsgBox without code interruption?
    By Riggers in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-20-2011, 07:45 AM
  5. I want the amendment to this code
    By hesham63 in forum Excel General
    Replies: 2
    Last Post: 08-05-2011, 06:41 PM
  6. Code To Check blank textbox in userform with yes no msgbox
    By jwala in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-01-2010, 05:32 AM
  7. Replies: 1
    Last Post: 11-29-2010, 06:56 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1