VBA code to pop up message from multiple values of cell B3

[SIPRIAN]

HI
I want a message box to automatically appear when Cell B3 from Sheet2 has one of the values below.

789, 456, 321, 741, 852

these values can be added in the script or I can add them on column B , B20 to B24

Thank you!

 

[Joe4]

Are the values being manually entered into B3, or is there a formula in cell B3?

When do you want this Message Box to be fired?
As values are being entered into B3, or on some other condition?

 

[SIPRIAN]

When one of the values( 789, 456, 321, 741, 852) are in cell B3 Sheet2 then message "stop" should pop out. The B3 value is copied from sheet 1 same worksheet
If the values can not be added in the script, I can add them on cells , B20 to B24 Sheet 2

 

[Joe4]

But are the values being MANUALLY copied into cell B3 (through Copy and Paste), or is it a formula in cell B3?
It is VERY important to understand how the values are getting there if you want to automate anything.

 

[SIPRIAN]

there is a formula in Sheet2 B3

=+Table_Query_12[supplier_id]

 

[SIPRIAN]

I can simplify the formula in Sheets2 B3 to
=Sheet1!D8

 

[Joe4]

The formula doesn't matter, just that it is a formula.

OK, that makes things a bit trickier, but see if this will work for you.
Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops up:

Private Sub Worksheet_Calculate()
    If Range("B3") = 789 Or Range("B3") = 456 Or Range("B3") = 321 _
        Or Range("B3") = 741 Or Range("B3") = 852 Then
        MsgBox "Stop!", vbOKOnly
    End If
End Sub

So ANY time ANY calculation is performed on that sheet, it will check the value in cell B3, and return "Stop!" if it is one of your five values.
The negative is that this code is triggered any time any calculation is done on the sheet (even if it is not cell B3).

The alternative is if cell D8 on Sheet1 is being updated manually and not by formulas, then we could do a Worksheet_Change event procedure code on Sheet1, watching cell D8 for updates.

 

[SIPRIAN]

Yey! Its working!

 

[My Aswer Is This]

Or use case like this:

Private Sub Worksheet_Calculate()
    'Modified  5/3/2022  1:07:35 PM  EDT
    Select Case Range("B3").Value
        Case 789, 456, 321, 741, 852
            MsgBox "Stop!", vbOKOnly
    End Select
End Sub

 

[SIPRIAN]

After doing some tests, the macro is working for values that it not suppose to work.
if cel B# in Sheet2 is 888, the message pops up and is not suppose to do that. Ho w can I make the message pop up only for those values?