Cell Change Macro Call

[rjplante]

I would like to have a macro launched when a cells value changes to "YES". I have the code listed below but it does not work. The cell has a formula that monitors the sheet to determine if it is complete. When it is, the value changes from 'NO" to "YES". Then I would like it to launch another macro. How do I do this?

--------------------------

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Count > 1 Then Exit Sub

Select Case Target.Address(False, False)

Case "BE5"
IF Range("BE5").value="YES" Then

Call MSTS_Update
Else
Exit Sub

End If

End Select

End Sub

 

[VoG]

Try

Private Sub Worksheet_Calculate()
If Range("BE5").Value = "YES" Then Call MSTS_Update
End Sub

 

[rjplante]

This didn't work. Does the macro it calls have to be in the same worksheet code window, or can it be in a workbook module?

 

[VoG]

It has to go in the worksheet's code module.

 

[rjplante]

Still no luck. What could be the issue? Running Office 2007.

 

[VoG]

I just tested with Excel 2010 using this and it worked

Private Sub Worksheet_Calculate()
If Range("BE5").Value = "YES" Then MsgBox "Boo"
End Sub

Edit: make sure that Events are enabled. Press CTRL + G then in the Immediate Window type

Application.EnableEvents=True

and press Enter.

 

[rjplante]

Still no luck. Can't afford the upgrade to 2010. Is there anything else I am missing?

 

[VoG]

That code should work in any version of Excel from 97 onwards.

Make sure that the code is in the right place. Right click the sheet tab, select View Code and paste in.

 

[rjplante]

Got it figured out. It works fine, but I now have a problem with the message box that pops up. The code is listed below, but when it pops up, and I click "NO" it chimes and then I have to click it twice before it closes the message box. What is going on?

------------------------

i = MsgBox("Do you want to update the Master Stability Tracking Sheet?", vbYesNo + vbExclamation + vbDefaultButton2)

If i = 7 Then 'NO
Exit Sub
ElseIf i = 6 Then 'YES

'the rest of the macro is here

Thanks,

Robert

 

[VoG]

Maybe you need to disable events, like this

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
If Range("BE5").Value = "YES" Then MsgBox "Boo"
i = MsgBox("Do you want to update the Master Stability Tracking Sheet?", vbYesNo + vbExclamation + vbDefaultButton2)

If i = 7 Then 'NO
Application.EnableEvents = True
Exit Sub
ElseIf i = 6 Then 'YES

'the rest of the macro is here

Application.EnableEvents = True
End Sub