Cell Change Record

**Greg777** · 10-26-2011, 02:45 AM

Is this possible: whenever the value of a cell changes that value and the time of the change is recorded?

For instance, if cell A1 begins with a value of 5, then changes to 4, that new value is recorded:

(4, 10/26/2011 1:42am)
...
and so forth.

The changes would be as the result of a DDE link; not a user typing into a cell. Thanks for any help!

Greg

**foxguy** · 10-26-2011, 09:12 PM

It can be done, but it's not easy (at least not for me).
Try this to get you started:

Sub StartCheckValues(Optional ByVal bStop as Boolean)
    Static dtStarted as Date
    Dim dt as Date

    If bStop Then
        Application.OnTime dtStarted, "CheckValues", Schedule:=False
    Else
        dt = Now + TimeValue("00:01:00")
        Application.OnTime dt, "CheckValues"
        dtStarted = dt
    End If
End Sub

Sub CheckValues()
    Dim r as Range
    With Sheets("Values")
        Set r = .Range("A1")
        Do While r.Value > ""
            If r.Value <> r.Offset(0,1).Value Then
                r.Offset(0,2).Value = r.offset(0,1).Value & " Changed To " & r.Value & " at "  & now
                r.Offset(0,1).Value = r.Value
            End If
            Set r = r.Offset(1)
        Loop
    End With
    StartCheckValues
End Sub

Sub StopCheckValues()
    StartCheckValues True
End Sub

Then run "StartCheckValues" to start checking and "StopCheckValues" to stop checking.
If you want to always be running when the workbook is open then put "StartCheckValues" in Workbook_Open()