Cell Change Macro Call

rjplante

Active Member
Joined
Oct 31, 2008
Messages
477
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
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

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

rjplante

Active Member
Joined
Oct 31, 2008
Messages
477
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?
 

rjplante

Active Member
Joined
Oct 31, 2008
Messages
477

ADVERTISEMENT

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
I just tested with Excel 2010 using this and it worked

Code:
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

Active Member
Joined
Oct 31, 2008
Messages
477

ADVERTISEMENT

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Active Member
Joined
Oct 31, 2008
Messages
477
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Maybe you need to disable events, like this

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,960
Messages
5,525,914
Members
409,671
Latest member
nasseralateek

This Week's Hot Topics

Top