VBA To Populate Pop Up Message In One Sheet, Based Off Cell Value In A Separate

[SHS1987]

Hello!

I'm trying to have a pop-up message occur on a tab ("Analytical Approach") that gives the user a warning, only if the value "3" is present between cells B4:B12 on a separate tab ("Control"). The cells in in the Control tab are populated based off of a Form Control button on Analytical Approach. So I'm hoping every time someone selects a value which populates 3 in this separate tab, the warning will pop up.

This is what I have so far...and it's gotten me nowhere

Private Sub Find_Criteria()

Dim r
r = Range(Sheets("Control").Range("B4:B12")).End(xlUp).Row


If Sheets("Control").Range("B") = "3" Then

MsgBox "Warning"

End If

Next i

End Sub

 

[StephenCrump]

The cells in the Control tab are populated based off of a Form Control button on Analytical Approach.

What code are you using for this?

Wouldn't here be the logical place to put the warning if a "3" is populated?

 

[SHS1987]

Thanks for the reply Stephen. The initial tab (Analytical Approach) is more of an intake form, so this is the only tab the user will see which is why I'd like the warning to pop up there. The Control tab is hidden from end-user view, and is used as a repository for other Form Control Buttons to control, and where many of the Vlookups are based.

 

[StephenCrump]

If I'm understanding you correctly:

- The user enters data on the "Analytical Approach" sheet
- The user clicks a button
- Code for that button determines whether or not a "3" is populated in Control!B4:B12

In which case, the logical place to generate a warning would be within that code?

If it's not the code, but formulae that determine whether or not a "3" is populated (?) we can code for the Worksheet_Calculate() event.