VBA Message Box if

[Will85]

I have some code that I want to execute, but I only want the code to execute if certain cells in a worksheet meet some conditions. If the conditions are not met, then I do not want my code to execute, and instead I want a message box to pop up with a message specific to which cell did not meet the condition.

Cells A1:A3 will only display a "Yes" or a "No"

If cell A1 or A2 or A3 = "No", then don't execute my code
If cell A1 was a "No", then display message box with text "Missing customer number"
If cell A2 was a "No", then display message box with text "Missing Date"
If cell A3 was a "No", then display message box with text "Missing Duration"

Its possible that more than one cell could display a "No" and I would prefer the message box to display all applicable errors at once.

If all three cells display a "Yes" then execute my code with no message box.

 

[rlv01]

Maybe this?

Option Explicit

Private Sub Worksheet_Activate()
    CellCheck
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Application.Intersect(Target, Me.Range("A1:A3")) Is Nothing Then
       CellCheck
   End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    CellCheck
End Sub

Private Sub CellCheck()
    Dim CellYes(1 To 3) As Boolean
    Dim I As Long
    Dim Msg As String
    
    For I = 1 To 3
        CellYes(I) = UCase(Me.Cells(1, I)) = "YES"
        
        If Not CellYes(I) Then
            Select Case I
                Case 1
                    Msg = Msg & "Missing customer number" & vbCr
                Case 2
                    Msg = Msg & "Missing Date" & vbCr
                Case 3
                    Msg = Msg & "Missing Duration" & vbCr
            End Select
        End If
    Next I
    
    If CellYes(1) And CellYes(2) And CellYes(3) Then
        'Call macro you want to run here
    Else
        MsgBox Msg, vbExclamation
    End If
End Sub