Cell Change Macro Call

rjplante

Well-known Member
Joined
Oct 31, 2008
Messages
572
Office Version
  1. 365
Platform
  1. Windows
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try

Code:
Private Sub Worksheet_Calculate()
If Range("BE5").Value = "YES" Then Call MSTS_Update
End Sub
 
Upvote 0
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?
 
Upvote 0
Still no luck. What could be the issue? Running Office 2007.
 
Upvote 0
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.
 
Upvote 0
Still no luck. Can't afford the upgrade to 2010. Is there anything else I am missing?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,218,560
Messages
6,143,204
Members
450,469
Latest member
brent3162

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
Back
Top