Get cell's old value on a worksheet change event

[zinstall]

Hi,

I would like to know how I can get the old value of a cell that has been changed in the moment when the worksheet triggers the change event for this cell.

Thanks a lot!

zinstall

 

[Andrew Poulsom]

Like this?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim NewValue, OldValue
    If Target.Address <> "$A$1" Then Exit Sub
    Application.EnableEvents = False
    With Target
        NewValue = .Value
        Application.Undo
        OldValue = .Value
        .Value = NewValue
    End With
    MsgBox "Old Value: " & OldValue & vbCrLf & "New Value: " & NewValue
    Application.EnableEvents = True
End Sub

 

[zinstall]

Thanks,

It's a good way to do it! However I am using a user form to change the content of the cell and so the undo is not working...

Is there another way?

 

[Airfix9]

The only thing that I can suggest is to code the UserForm to place the cell's old value on a hidden sheet - kind of like a change tracker. Using Environ("Username"), you could even log who made the change, what time etc...

 

[Andrew Poulsom]

Thanks,

It's a good way to do it! However I am using a user form to change the content of the cell and so the undo is not working...

Is there another way?

In that case why not assign the cell's value to a variable before changing it?

 

[zinstall]

OK, I've done it and it works well!

thanks!

 

[TCEMR]

BRILLIANT!! I have been trying to figure this out for months. Thank you!!

 

[HPFruity]

In that case why not assign the cell's value to a variable before changing it?

Hi,

I've been looking for this answer for a while, Could someone possibly paste a code with the suggested variable before changing it?

 

[TCEMR]

I subsequently have found that Application.Undo freqently causes an error. When making changes directly to a cell, you have to capture the old value prior to the Worksheet_Change Event. I store the original value of a cell that is clicked to a Public variable using the Worksheet_SheetSelectionChange event. (OldValue = Target.Value) Works great!