Execute macro only if specific cell value changes

Question

I have a simple countif formula in Cell E2 that will check for a specific text. Once it's true, it'll execute a macro that will prompt for a msgbox. This code works fine, but when making any other changes to the worksheet will execute the macro again, even though Cell E2's value hasn't changed. How do I stop the macro from executing any further if E2 does not change at all?

Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("E2")) Is Nothing Then If Target.Value = "True" Then Application.EnableEvents = False a = MsgBox("Test", vbYesNo, "Test") If a = vbYes Then Range("E3") = "003" Else Range("E3") = "001" End If Call ApplyMG Application.EnableEvents = True End If End If End Sub

EDIT: Thanks to the comment below, removed 'old code' setting target to the same range in the intersect line. However, the macro is not being triggered anymore.

Silly me, I mixed different codes together and now this became a mess. Thanks for the tip, now the intersect finds the values I changed manually. — Basher
– Basher, Commented Nov 5, 2018 at 19:51
Most likely it isn't triggering now because the code has been canned half way so it hasn't reached the point of re enabling the events, go to the debug window (ctrl-G from the VBE and type Application.EnableEvents = True and press enter, this should fix it for you. — Dan Donoghue
– Dan Donoghue, Commented Nov 5, 2018 at 20:20

Basher · Accepted Answer · 2018-11-05 20:12:36Z

Well turns out I was overcomplicating things. After some research, what I'm trying to do won't work on Worksheet_Change in the first place as this is a formula. So, I only need to move my code onto Worksheet_Calculate instead. No need for unnecessary intersect or anything, as my code only needs to determine if Cell E2 is True, which is determined by the cell formula. Nothing else would matter anyway.

Private Sub Worksheet_Calculate() Dim trigger As Range Set trigger = Range("E2") If trigger.Value = "True" Then Application.EnableEvents = False a = MsgBox("Test", vbYesNo, "Test") If a = vbYes Then Range("E3") = "003" Else Range("E3") = "001" End If Call ApplyMG Application.EnableEvents = True End If End Sub

VBasic2008 · Accepted Answer · 2021-01-30 14:37:52Z

A Real Solution

The result of a CountIf formula is a whole number or an error. So you probably have an If statement that is evaluating a CountIf formula in cell E2.
Use True without the quotes (NOT "True") if the result is a boolean (True or False).
You should always use Option Explicit to be forced to declare all variables (referring to 'a').
Use constants at the beginning of the code to be able to quickly change values in one place only, if necessary.
In your answer, the code is still running all the time (when the worksheet is being calculated) if the value in E2 is True, regardless if the value has changed or not.
The following code might have been written using a static variable inside the procedure instead of the chosen module level variable (blnCheck) outside the procedure. (Should be investigated.)

Option Explicit Private blnCheck As Boolean Private Sub Worksheet_Calculate() Const cStrRangeCheck As String = "E2" Const cStrRangeWrite As String = "E3" Const cStrResultYes As String = "003" Const cSTrResultNo As String = "001" Dim Msg As Variant Dim blnTarget As Boolean If IsError(Range(cStrRangeCheck).Value) Then GoTo TargetHandler blnTarget = Range(cStrRangeCheck).Value If blnTarget = True Then If blnCheck = False Then blnCheck = True Application.EnableEvents = False Msg = MsgBox("Test", vbYesNo, "Test") If Msg = vbYes Then Range(cStrRangeWrite) = cStrResultYes Else Range(cStrRangeWrite) = cSTrResultNo End If ApplyMG Application.EnableEvents = True End If Else 'blnTarget = False If blnCheck = True Then blnCheck = False End If End If ProcedureExit: Exit Sub TargetHandler: MsgBox "blnTarget has to be a boolean." GoTo ProcedureExit End Sub

Collectives™ on Stack Overflow

Execute macro only if specific cell value changes

2 Answers 2

Comments

A Real Solution

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

A Real Solution

Comments

Related