Get cell's old value on a worksheet change event

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jan 21, 2008

• #2

Like this?

Code:

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

 

Upvote 0

Z

zinstall

New Member

Joined

Jan 18, 2008

Messages

3

• Jan 21, 2008

• #3

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?

 

Upvote 0

Airfix9

Well-known Member

Joined

Sep 23, 2005

Messages

886

• Jan 21, 2008

• #4

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...

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• Jan 21, 2008

• #5

zinstall said:

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?

Click to expand...

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

 

Upvote 0

Z

zinstall

New Member

Joined

Jan 18, 2008

Messages

3

• Jan 22, 2008

• #6

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

thanks!

 

Upvote 0

T

TCEMR

New Member

Joined

Mar 21, 2015

Messages

14

• Mar 21, 2015

• #7

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

 

Upvote 0

H

HPFruity

New Member

Joined

May 7, 2020

Messages

28

Office Version

1. 365

Platform

1. Windows

• May 7, 2020

• #8

Andrew Poulsom said:

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

Click to expand...

Hi,

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

 

Upvote 0

T

TCEMR

New Member

Joined

Mar 21, 2015

Messages

14

• May 7, 2020

• #9

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!

 

Upvote 0