Hi Goody,

The following code example gets the previous entry by using Undo. However,
note that if the user makes an error in entering data and has to re-enter
then it does not get the original data; only the data there immediately prior
to the last entry. Running the code also cancels the undo's in the
interactive mode.

The code is really only suitable where you want to use code to validate an
entry and probably put it back to original value if new value is not valid.
See my second example. (Note normal validation would normally be used in lieu
of the code example and the code is only a simplistic example.)

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Re_EnableEvents
Application.EnableEvents = False

Dim newValue As Variant
Dim prevValue As Variant

newValue = Target.Value
Application.Undo

prevValue = Target.Value
Target.Value = newValue

MsgBox "Previous value of " _
& Target.Address & " was: " _
& prevValue

Re_EnableEvents:
Application.EnableEvents = True
End Sub


Entry validation.
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo Re_EnableEvents
Application.EnableEvents = False

Dim newValue As Variant
Dim prevValue As Variant

newValue = Target.Value
Application.Undo

prevValue = Target.Value

If newValue <= prevValue Then
MsgBox "The value you entered is invalid." _
& vbLf & "Please re-enter."
Else
Target.Value = newValue
End If

Re_EnableEvents:
Application.EnableEvents = True
End Sub


--
Regards,

OssieMac


"Goody" wrote:

I am using the Worksheet_Change event to trigger a macro saving the new cell
value to a VBA variable. I use the Worksheet_SelectionChange event to capture
the old value before the change occurs. How can I trigger a macro when the
cell being changed is already selected? In other words, is there a way to
capture the old value as soon as the editing begins, then capture the new
value when editing ends?

Goody