How to Detect if a Cell is Changed by an “=IF()” F

Question

I read a lot of pages saying that, but none of them put the solution if the value change by an "if function" not by hand.

The code I get is that:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("A18:A30")) Is Nothing Then Exit Sub
    Application.EnableEvents = False 'to prevent endless loop
    On Error GoTo Finalize 'to re-enable the events
    MsgBox "You changed THE CELL!"
Finalize:
    Application.EnableEvents = True
End Sub

It only works if I change the value by hand.

Thank you in advance.

Answer 1

Another solution; instead of triggering your function every time when your worksheet recalculates, add a function in a module:

Function DetectChange() As Integer
    MsgBox "You changed THE CELL!"
    DetectChange = 0
End Function

Assuming the outcome of your formula is numeric:(otherwise outcome of function must be a empty string and the "+" must be "&") Add to your IF-formula at the end ...+Detectchange() Now there will be a msgbox only when your formula is recalculated

Edit by Darren Bartrup-Cook:

I found this code gave worked when the formula recalculated. It didn't fire if I changed a cell that doesn't affect the cell it's entered to and it didn't fire using Calculate Now or Calculate Sheet.
It did occasionally fire for all formula that I used the function in, but that seemed to be when I was debugging - maybe further investigation needed.

Public Function DetectChange()
    MsgBox "You changed cell " & Application.Caller.Address
End Function  

e.g.:
=IF(A1=1,A2,A3) & DetectChange() entered in cell A4 displays the message "You changed cell $A$4" if cells A1, A2 or A3 is changed.

Answer 2

Write this in Sheet1 and run the TestMe sub:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Me.Range("A1:A30")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo Finalize
    MsgBox "You changed THE CELL!"
Finalize:
    Application.EnableEvents = True
End Sub

Sub TestMe()
    Range("A1") = 34
End Sub

It has worked quite ok on my PC.

---

If the cell is changed by a built-in Excel function, then the comment of @Vincent G states the correct answer:

Worksheet_Change event occurs when cells on the worksheet are changed by the user or by an external link. and This event does not occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation.

---

If you want to track the calclulation event based on some changes at Range(A18:A30) this is a working solution:

• Add a new Worksheet to your Workbook (Sheet2);

• In the current Worksheet write the Calculate event:

  Private Sub Worksheet_Calculate()        
      Dim cell As Range
      For Each cell In Sheet2.Range("A18:A30")
          If cell <> Sheet1.Range(cell.Address) Then
              cell = Sheet1.Range(cell.Address)
          End If
      Next cell
  End Sub
  

• In the Sheet2 write an event, catching the changes.

Answer 3

As simple as @Vincent G says.

Private Sub Worksheet_Calculate()

     Call YourFunction

End Sub