Newbie - cell contents B4 change event ?

W

Walter

Dear All

Apologies if you have answered this question a thousand times before
but I can't seem to find the answer, and my I can't seem to find th
right search term to get the answer. Must not be enough of a latera
thinker.

---------------

I would like to be able to store the contents of a cell before it i
changed and a worksheet_change event is triggered.

This will allow me to revert back to this value if a user presses
'Cancel' button on a form I'm developing.

The only thing I've been able to find that's close is th
'BeforeUpdate' event, but it seems this doesn't apply to worksheets
cells.
 
J

Julian Milano

Walter,

The following code will do what you want in a basic format:

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

It stores the value of the cell clicked on. When the cursor is moved to
another cell, A1 takes the initial value of *that* cell.
 
D

Darren Hill

Create a Static variable in which to store the value.

In the worksheet_change, use something like
====================
Static OldValue
Dim NewValue

OldValue = NewValue
NewValue = Target.value

===================
After the first time it runs, you'll have the old value and the new, changed
value.

You could also (or instead) store the oldvalue in a hidden cell somewhere,
so you can preserve its value between sessions.

As so:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldValue, NewValue

NewValue = Target.Value
OldValue = Range("the_Cell_I_Use_To_Store_This_Value").Value
' do whatever you want

' then store the current value
Range("the_Cell_I_Use_To_Store_This_Value").Formula = NewValue

End Sub
 
Top