Cell Change Macro Call

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
538
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

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

Well-known Member
Joined
Oct 31, 2008
Messages
538
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

Well-known Member
Joined
Oct 31, 2008
Messages
538

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

Well-known Member
Joined
Oct 31, 2008
Messages
538

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

Well-known Member
Joined
Oct 31, 2008
Messages
538
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,017
Messages
5,834,960
Members
430,330
Latest member
drAli77

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top