How to get original cell value prior to change event?

[Kelvin Stott]

Hello,

I'm struggling to find a way to record the original value of a cell prior to change (rather than just the new value), so that I can use it in my change event procedure. Any ideas?

 

[Firefly2012]

Hi Kelvin

You need to have stored it prior to the change in typically either:

1. Another cell
2. A workbook name
3. A Public or Globally scoped variable

So that then you can reference this to access the original value.

If you want help on how you might apply this, can you explain how you want to use this?

 

[Kelvin Stott]

Is it possible to use the change event itself to record the number *before* it is changed, similar to the before save event, or does the change event only follow the change?

 

[Firefly2012]

No - during the change event is too late I'm afraid. MS could have implemented the Change event to include a variable holding the old value (eg TargetOld perhaps) but they didn't (possibly down to memory considerations).

 

[nuked]

Something like this might (crudely) be of some use.

Option Explicit

Public vPrevious As Variant

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, [A1]) Is Nothing Then

    Debug.Print vPrevious
    Debug.Print Target.Value
    
End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

vPrevious = Target.Value

End Sub