Help needed with message box VBA!

[VincentW29]

Morning all,

Apologies, posting this because I could not find appropriate solutions to my problem. Earlier I obtained this particular code from Dante (which I’m immensely thankful for) which helped in generating a message box (when two conditions are met) for my worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
  If Not Intersect(Target, Range("D4, H7")) Is Nothing Then
    If Range("D4").Value = "Supplier" And Range("H7").Value = "Shipment" Then
      MsgBox "two conditions are met"
    End If
  End If
End Sub

However, I need help in enhancing this to include additional message boxes within the same worksheet (which I’ve tried to do and failed). The items I would like to generate message boxes for are:
- if cell A2 is above a certain date
- if either cell E10, F10 or G10 meets a certain criteria (from a dropdown list)
- if the value in cell J5 is over 100000

Most of all, I would like to know how to structure the code such that multiple message boxes can appear in the same worksheet.. really appreciate any help I can get here! Thank you!

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

I'm not sure what your E10:G10 criteria is so I have just put a comment in that section but hopefully the structure of this code is the sort of thing you are asking about.

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .CountLarge = 1 Then
      Select Case True
        Case .Address = "$D$4" Or .Address = "$H$7"
          If Range("D4").Value = "Supplier" And Range("H7").Value = "Shipment" Then MsgBox "two conditions are met"
        Case .Address = "$A$2"
          If IsDate(Range("A2").Value) And Range("A2").Value > DateSerial(2022, 3, 27) Then MsgBox "After date"
        Case Not Intersect(.Cells, Range("E10:G10")) Is Nothing
'          If E10 or F10 or G10 meets a criteria then MsgBox "E10:g10 criteria met"
        Case Address = "$J$5"
          If Range("J5").Value > 100000 Then MsgBox "J5 > 1000000"
      End Select
    End If
  End With
End Sub

 

[VincentW29]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

I'm not sure what your E10:G10 criteria is so I have just put a comment in that section but hopefully the structure of this code is the sort of thing you are asking about.

Private Sub Worksheet_Change(ByVal Target As Range)
  With Target
    If .CountLarge = 1 Then
      Select Case True
        Case .Address = "$D$4" Or .Address = "$H$7"
          If Range("D4").Value = "Supplier" And Range("H7").Value = "Shipment" Then MsgBox "two conditions are met"
        Case .Address = "$A$2"
          If IsDate(Range("A2").Value) And Range("A2").Value > DateSerial(2022, 3, 27) Then MsgBox "After date"
        Case Not Intersect(.Cells, Range("E10:G10")) Is Nothing
'          If E10 or F10 or G10 meets a criteria then MsgBox "E10:g10 criteria met"
        Case Address = "$J$5"
          If Range("J5").Value > 100000 Then MsgBox "J5 > 1000000"
      End Select
    End If
  End With
End Sub

Hey Peter!
Thank you for sharing your knowledge, it is really helpful and the code totally works!

Thank you so much!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.