Before Change Event?

[Guest]

Does Excel have a BEFORE change event in a worksheet? I'd like to capture
the value of a cell before it was changed in addition to what it was changed
to.

I understand that in the Private Sub Worksheet_Change(ByVal Target As Range)
procedure, Target.Address and Target.Value can be used to identify what cell
was changed and what value it was changed to, but it would be useful to know
what the original cell value was. Thanks!

 

[Guest]

You need to create a global variable to hold the value and then use the
selection change and sheet activate events to capture the value prior to it
being changed...

Private m_varMyValue As Variant

Private Sub Worksheet_Activate()
m_varMyValue = Range("A1").Value
End Sub

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

You may also want to capture the value when the spreadsheet is opened. If so
then use the event code in thisworkbook to populate the variable (which will
need to be changed to public)...

 

[Don Guillett]

Option Explicit
Dim oldvalue As Double

Private Sub Worksheet_Change(ByVal target As Excel.Range)
If target.Address = "$A$5" Then
On Error GoTo fixit
Application.EnableEvents = False
If target.Value = 0 Then oldvalue = 0
target.Value = 1 * target.Value + oldvalue
MsgBox oldvalue
oldvalue = target.Value
fixit:
Application.EnableEvents = True
End If
End Sub

 

[Guest]

Thank you both for the help!
--
Steve C

Option Explicit
Dim oldvalue As Double

Private Sub Worksheet_Change(ByVal target As Excel.Range)
If target.Address = "$A$5" Then
On Error GoTo fixit
Application.EnableEvents = False
If target.Value = 0 Then oldvalue = 0
target.Value = 1 * target.Value + oldvalue
MsgBox oldvalue
oldvalue = target.Value
fixit:
Application.EnableEvents = True
End If
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)