vbamsgbox

VBA MsgBox pop-up according to cell value


I'm trying to set up a spreadsheet to return a message box whenever a cell value is entered, I have the code below which works fine for just 1 cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A As Range
    Set A = Range("D5")
    If Intersect(Target, A) Is Nothing Then Exit Sub
    If Target.Value = "Yes" Then
        MsgBox "Message"
    End If
End Sub

I need to have a MsgBox pop up whenever either of these 3 are met: D5="Yes", B5="No" and B13="Submit form", however I can't make it pop up for more than 1 cell at a given time either writing in one module or 3 separate ones. Each one of the Boxes for each one of those cells should return a different message, i.e., D5="Yes" MsgBox "Please fill out..." or B5="No" MsgBox "Submit Form"


Solution

  • Just include all the tests in the event sequentially.

    This code will also handle more than one cell changing at once (ie Target containing more than one cell), which your original code won't

    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim A As Range
    
        Set A = Me.Range("D5")
        If Not Intersect(Target, A) Is Nothing Then
            If A = "Yes" Then
                MsgBox "Message for D5"
            End If
        End If
    
        Set A = Me.Range("B5")
        If Not Intersect(Target, A) Is Nothing Then
            If A = "No" Then
                MsgBox "Message for B5"
            End If
        End If
    
        Set A = Me.Range("B13")
        If Not Intersect(Target, A) Is Nothing Then
            If A = "Submit form" Then
                MsgBox "Message for B13"
            End If
        End If
    End Sub