How to Detect if a Cell is Changed by an “=IF()” Formula and not by a User

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.

A18:A30

A18:A30

MsgBox()

3 Answers
3

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.

Calculate Now

Calculate Sheet

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.

=IF(A1=1,A2,A3) & DetectChange()

Application.Volatile(False)

Application.EnableEvents = False

MsgBox "You changed cell " & Application.Caller.Address

DetectChange = 0

Integer

=SUM(B2:B3)+DetectChange()

=D2 & D3 & DetectChange()

Write this in Sheet1 and run the TestMe sub:

Sheet1

TestMe

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:

Range(A18:A30)

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.

Sheet2

If function

As simple as @Vincent G says.

Private Sub Worksheet_Calculate()

Call YourFunction

End Sub


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.