Trapping value before and after change

[Goody]

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

 

[Barb Reinhardt]

The worksheet_Selection change event is only triggered if you actually make a
change in selection. If you enter something in Call A2 and then re-enter
something in the same cell, it won't be triggered the 2nd time. The only
way I can see around this is to capture the value at the end of the worksheet
change event. If you do a selection change, the value will be replaced then.

 

[OssieMac]

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

 

[Goody]

I found a workaround. When the worksheet is activated, I stored the values of
all cells in the area most likely to contain any modificationsin an array.
Thus, even if the selection is not changed, the value is stored. Then, when a
cell is changed, I get the previous value from the array.

Thanks for your help.