View previous cell value programmically

[strataguru]

I have a vba macro that is kicked off from ThisWorkbook whe
Workbook_SheetChange event occurs.

When the user inputs or updates a value in a cell - I'd like to kno
the previous value. What object will provide me with tha
information?

Thanks!
-Robi

 

[Guest]

hi,
i dont' think you can. once changed and you aint' there to
see it...it's gone. you could write code that copies all
NEW entries to a history sheet/file with control name, new
value, date, time, ect. you could then search past data to
find it.

 

[Bernie Deitrick]

Robin,

Use this in the change event:

Dim NewVal As Variant
Dim OldVal As Variant

If Target.Cells.Count <> 1 Then Exit Sub

NewVal = Target.Value
With Application
.EnableEvents = False
.Undo
OldVal = Target.Value
.Undo
.EnableEvents = True
End With
MsgBox "The old value was " & OldVal & Chr(10) & _
"The new value is " & NewVal

HTH,
Bernie
MS Excel MVP