Newbie - cell contents B4 change event ?

  • Thread starter Thread starter Walter
  • Start date Start date
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.
 
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.
 
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
 
Back
Top