PDA

View Full Version : Solved: How to capture cell changes



nst1107
01-10-2009, 09:14 PM
I can use Workbook_SheetChange to capture when a user or an external link changes the value in a cell, but is there a way to capture when the value of a cell's formula changes, besides using Worksheet_Calculate? Specifically, I'm looking for a way to determine which cell had its value changed by its formula.

mikerickson
01-10-2009, 11:50 PM
This prints a message everytime that C1 has its formula re-calculated. A static variable could be used to determine if the value has changed.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Target
On Error Resume Next
If Application.Intersect(.Dependents, Range("c1")) Is Nothing Then
Else
On Error GoTo 0
MsgBox "The formula in C1 has been re-calculated"
End If
On Error GoTo 0
End With
End Sub

nst1107
01-11-2009, 10:26 AM
Wonderful. Thanks!