Trigger Event Code

[Guest]

I have a formula in cell B1 that is linked to several other cells. If
various condidtions are met, the value in cell B1 will equal, "Yes".
Otherwise, it equals, "No".

Is there a way I can trigger a macro anytime the value in Cell B1 changes
from "No" to "Yes". I would like for a message box to pop-up with a warning.

 

[Norman Jones]

Hi Shawn,

Try:
Private Sub Worksheet_Calculate()
Dim rng As Range
Static blFlag As Boolean
Set rng = Range("B1")

If rng.Value = "Yes" Then
If Not blFlag Then
MsgBox "No has changed to Yes", _
vbInformation, "ALERT!"
blFlag = True
End If
Else
blFlag = False
End If

End Sub


This is worksheet event code and needs to be placed in the sheet module
behind the worksheet in question, *not* in a standard module.

So, access the sheet module by right-clicking the sheet tab, select 'View
Code', paste the above code, Alt-F11 to return to the worksheet.

 

[Losse]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address = "$B$1" Then
Application.EnableEvents = False
If Target.Value = yes Then
Msgbox ("B1 = Yes")
Application.EnableEvents = True
End I