locked
VBA msg box - combining codes into 1 query RRS feed

  • Question

  • I have the following VBA coding which works perfect for 1 msg pop up box. However I want to add 2 other pop up boxes in the same query, as i know this can't be more than 1, otherwise I will get a 'compile error'.

    *Here is the VBA, then below the coding, i have put the other details i want to incorporate*

      Dim xCell As Range, Rg As Range

        On Error Resume Next
        Set Rg = Application.Intersect(Target, Range("A3:A600"))
        If Not Rg Is Nothing Then
            For Each xCell In Rg
                If xCell.Value = "New" Then
                    MsgBox "You have selected new"
                    Exit Sub
                End If
            Next
        End If
    End Sub
    Private Sub Worksheet_selectionChange(ByVal Target As Range)
        Dim xCell As Range, Rg As Range
        On Error Resume Next
        Set Rg = Application.Intersect(Target, Range("A3:A600"))
        If Not Rg Is Nothing Then
            For Each xCell In Rg
                If xCell.Value = "New" Then
                    MsgBox "The following details are mandatory:" & vbNewLine & "* Funding Source" & vbNewLine & "* Contract Category" & vbNewLine & "* Canadian or Non-Canadian" & vbNewLine & "* Effective Start Date" & vbNewLine & "* Effective End date" & vbNewLine & "* Unique Identifier" & vbNewLine & "* Value of contract"
                    Exit Sub
                End If
            Next
        End If
    End Sub

    Want I want to include into this coding is (and I know when this goes into the one above, it wont look like this):

       Set Rg = Application.Intersect(Target, Range("A3:A600"))
        If Not Rg Is Nothing Then
            For Each xCell In Rg
                If xCell.Value = "Amendment" Then
                    MsgBox "You have selected amendment"

    &

       Set Rg = Application.Intersect(Target, Range("A3:A600"))
        If Not Rg Is Nothing Then
            For Each xCell In Rg
                If xCell.Value = "Old" Then
                    MsgBox "You have selected old"

    Friday, November 17, 2017 2:15 PM

All replies

  • martin,
    re:  content of your post

    There is no title line for the first code set you posted.
    Also, that code is similar to the selection change code; why?
    • Edited by James Cone Friday, November 17, 2017 3:06 PM
    Friday, November 17, 2017 3:05 PM
  • Hi marti_a1156,

    What do you mean two other pop up boxes? When will you get the compile error?

    If you want to show different pop up boxes according to selected cell, you could use selected...case statement.

    If you want to show two pop up boxes when selected value is "New", you could put the msgbox code together so once you close the first pop up box, the second will show.

    Here is the simply code and demonstrate. Did I misunderstand anything?

    Private Sub Worksheet_selectionChange(ByVal Target As Range)
        Dim xCell As Range, Rg As Range
        On Error Resume Next
        Set Rg = Application.Intersect(Target, Range("A3:A600"))
        If Not Rg Is Nothing Then
            For Each xCell In Rg
                Select Case xCell.Value
                Case "New"
                MsgBox "You have selected new"
                MsgBox "The following details are mandatory:" & vbNewLine & "* Funding Source" & vbNewLine & "* Contract Category" & vbNewLine & "* Canadian or Non-Canadian" & vbNewLine & "* Effective Start Date" & vbNewLine & "* Effective End date" & vbNewLine & "* Unique Identifier" & vbNewLine & "* Value of contract"
                Exit Sub
                Case "Old"
                MsgBox "You have selected old"
                Exit Sub
                Case "Amendment"
                MsgBox "You have selected amendment"
                Exit Sub
                End Select
            Next xCell
        End If
    End Sub

    Best Regards,

    Terry


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, November 20, 2017 2:38 AM