VBA Message Box if

Will85

Board Regular
Joined
Apr 26, 2012
Messages
191
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,081
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Maybe this?

VBA Code:
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
 

Forum statistics

Threads
1,144,370
Messages
5,723,960
Members
422,529
Latest member
mbilal429

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top