How Capture Cell Value Before Change Event Fires?

[Guest]

I am working with the Worksheet change event to perform cell level edits. How
do I get the value of a selection prior to the change event? I assume the
value is stored somewhere in order to enable the undo event to work. I also
read that using the worksheet_Change event disables the undo.

Am I required to write a Worksheet_BeforeChange event, or can I just get the
value so I know what it is?

Thanks in advance

 

[Jim Rech]

Wherever the old values is stored they are not accessible to us. So..

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldValue As Variant
With Application
.EnableEvents = False
.Undo
OldValue = Target.Cells(1).Value
.Undo
.EnableEvents = True
End With
MsgBox "Value was " & OldValue
End Sub

This mades the simplifying assumption only one cell was changed. In 'real
life' you'd need to capture the values in the entire Target range, which can
be more than one cell.

--
Jim
|I am working with the Worksheet change event to perform cell level edits.
How
| do I get the value of a selection prior to the change event? I assume the
| value is stored somewhere in order to enable the undo event to work. I
also
| read that using the worksheet_Change event disables the undo.
|
| Am I required to write a Worksheet_BeforeChange event, or can I just get
the
| value so I know what it is?
|
| Thanks in advance

 

[Rick Rothstein \(MVP - VB\)]

I am working with the Worksheet change event to perform cell level edits.

How
do I get the value of a selection prior to the change event? I assume the
value is stored somewhere in order to enable the undo event to work. I
also
read that using the worksheet_Change event disables the undo.

Am I required to write a Worksheet_BeforeChange event, or can I just get
the
value so I know what it is?

There is already a kind of BeforeChange event... it is called
SelectionChange. Add this code to your worksheet's code window...

Dim LastText As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
LastText = Target.Value
End Sub

and, when in the Change event, you can reference LastText to see what the
text was when you entered the cell. Note that the Dim statement for the
LastText statement is NOT declared inside any procedures... just place it at
the top of the code window, under your Option Explicit statement if you are
using one (and you should be using one<g>).

Rick