Multiple MsgBox based on various cell values

[DarrenK]

I am just picking up the MsgBox macro in VBA and have come to a snag.

I'm working on a file for our company trainer to jazz up their training class with an Excel game. Within the game, I am counting the number of times a 1 appears within a certain group of cells. Those 1's are hidden and correspond to another group of cells that the trainees can see on the projector. Once the number of 1's within the range adds up to 25, I have a popup window to let the team know they won. (Please see below).

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("BE16")) Is Nothing Then Exit Sub
If Target.Value = "25" Then Exit Sub
MsgBox "CONGRATULATIONS TEAM 1! You are the winners!! "
End Sub

Team 1's points add up in cell BE16 and Team 2's points are tallied in cell BF16. It seems straightforward but I cannot seem to formulate the macro to allow for different popup windows.

Within one Worksheet_Change macro, I have seen examples of multiple MsgBoxes based on <,>, similar to CF. However, I would like to see about having 1 box appear if BE16 reaches 25 points and another box to appear if BF16 reaches 25. Is that possible within 1 macro?

Thank you in advance for any assistance offered.

 

[Scott T]

I think this should work for you.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("BE16") < 25 And Range("BF16") < 25 Then Exit Sub
If Range("BE16") >= 25 Then
    MsgBox ("CONGRATULATIONS TEAM 1! You are the winners!!")
    Exit Sub
End If
If Range("BF16") >= 25 Then
    MsgBox ("CONGRATULATIONS TEAM 2! You are the winners!!")
    Exit Sub
End If

End Sub

Note that target in this line is referring to the cell that is changing to trigger the change event code not the cell BE16 unless that is the one you are changing.

If Target.Value = "25" Then Exit Sub

Note that change events are not triggered by formulas updating. I assume that there are formulas in BE16 and BF16 to add up the points so I do not test if they changed. You may want to stop the code if the change is outside of a certain range. If you put this as the first line of code then it will only run the rest of the code if the cell that changed is in the range A1:S77, change to match your data.

If Intersect(Target, Range("A1:S77")) Is Nothing Then Exit Sub

 

[DarrenK]

That absolutely worked perfect. Thank you Scott T. I appreciate the explanation as to why my formula wasn't working. One of these days I will need to find and take a class to really understand VBA code. Thank you again!!

 

[Scott T]

That absolutely worked perfect. Thank you Scott T. I appreciate the explanation as to why my formula wasn't working. One of these days I will need to find and take a class to really understand VBA code. Thank you again!!

Have a look at his thread by hiker95 that list may resources.

https://www.mrexcel.com/forum/excel-questions/978868-starting-excel-post4696884.html#post4696884

 

[DarrenK]

Thank you again!! Nailed both questions of mine.