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:
- In Cell C3, enter: =IF(D3<100,1,2)
- In Cell D3, enter: 105
- 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 
