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"
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