Run Macro when cell result changes by formula – but a problem with unwanted macro re-trigger

Question

I have a question related to the info provided in the other question “Run Macro when cell result changes by formula“ Run Macro when cell result changes by formula and I was asked to create a new Question. I also read the reply in the "excel VBA run macro automatically when a cell is changed "https://stackoverflow.com/questions/38083021/excel-vba-run-macro-automatically-when-a-cell-is-changed but I struggle to get the different answers/pieces togehter for what i want to achieve.

The code example I added below will trigger Macro_01 if the formula in Cell C3=1

If the formula in C3 instead gives that C3 is changed from 1 to 2, then the Macro_02 will instead be triggered.

The Cell formula in C3 is an IF statement with only two possible values, 1 or 2

The problem I have is that Macro_01 or Macro_02 will also be re-triggered (again and again…) every time any other values/cells in the Sheet is updated (either automatic or manual) – and this is not what I want.

For example: If the IF formula in C3 becomes 1 (from previously being 2) – Macro_01 will correctly be triggered, BUT if value 1 stay in cell C3 because the IF statement is not changed, and another totally unrelated cell is updated or recalculated - for example Cell E12 have =RAND()+9 and you then hit Enter – then cell E12 will update , AND Macro_01 will be triggered again – and I don’t want the Macro_01 is triggered without a change in Cell C3.

Note that if you (as a test) instead manually enter for example the value 5 or text “hello” in the cell C3, then the VBA code will not be triggered again when something else in the Sheet is updated.My assumption/question is that there is something related to that the formula in C3 is triggered every time that it is a formula.

The example code below will trigger two different Macros where Macro_01 will either enter the text “Hi_01” and then “There_01” one second later in Cell A3, OR Macro_02 will instead enter “Hi_02” and then “There_02” in the same cell A3

The Test setup:

Open an empty Excel Sheet1 and enter:

1. In Cell C3, enter: =IF(D3<100,1,2)
2. In Cell D3, enter: 105
3. In Cell E12, enter: =RAND()+9

Cell C3 will display 2 because 105 is more than 100

To highlight the problem/question:

a) In Cell D3, enter instead the value 89 => Cell C3 will be changed to 1 because 89 os lower then 100 given the IF statement => the Macro_01 will correclty be triggered => The Cell A3 will first display “Hi_1” and then one second later “There_1”. This is All good!

b) Then, mark Cell E12 and hit Enter => E12 will be recalculated => BUT the Macro_02 will also (wrongly) be triggered as well (C3 still is still set to 1 because 89 is lower than 100) => The Cell A3 will first display “Hi_2” and then one second later “There_2”. Hit Enter again in E12 and the Marcro_02 is wrongly triggered once again. This is not good!

c) Override the content in Cell C3 and manually enter for example the value 5 or just "hello"=> the Macro_01 will (correctly) not be triggered, and not the Macro_02 again either. This is just a hypotetical example to illustrate that the error is related to the fact that it is a formula that get wrongly triggered.

The Code

Module1

Option Explicit

Public TargetValue As Variant
Private Const cTarget As String = "C3"

Sub TargetCalc(ws As Worksheet)
    If ws.Range(cTarget) = 1 Then
        Application.EnableEvents = False
        Macro_01
        TargetValue = ws.Range(cTarget).Value
        Application.EnableEvents = True
        
        ElseIf ws.Range(cTarget) = 2 Then
        Application.EnableEvents = False
        Macro_02
        TargetValue = ws.Range(cTarget).Value
        Application.EnableEvents = True
End If
End Sub

Sub TargetStart()
    TargetValue = Sheet1.Range(cTarget).Value
End Sub
Sub Macro_01()
'
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Hi_01"
    Application.Wait Now + TimeValue("0:00:01")
    ActiveCell.FormulaR1C1 = "There_01"
End Sub
Sub Macro_02()
'
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Hi_02"
    Application.Wait Now + TimeValue("0:00:01")
    ActiveCell.FormulaR1C1 = "There_02"
End Sub

ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    TargetStart
End Sub

Sheet1

Option Explicit

Private Sub Worksheet_Calculate()
    TargetCalc Me
End Sub

Answer 1

You need to test if the value changed

If ws.Range(cTarget) <>  TargetValue Then
    Application.EnableEvents = False
    Select Case ws.Range(cTarget).Value
        Case 1
            Macro_01
        Case 2
            Macro_02
    End Select
    TargetValue = ws.Range(cTarget).Value
    Application.EnableEvents = True
End If