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!
--
Steve C

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)...

--
HTH...

Jim Thomlinson


"Steve C" wrote:

> 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!
> --
> 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 Removed)
"Steve C" <(E-Mail Removed)> wrote in message
news:CB50F9B4-5A70-495D-BC67-(E-Mail Removed)...
> 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!
> --
> Steve C

Thank you both for the help!
--
Steve C


"Don Guillett" wrote:

> 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 Removed)
> "Steve C" <(E-Mail Removed)> wrote in message
> news:CB50F9B4-5A70-495D-BC67-(E-Mail Removed)...
> > 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!
> > --
> > Steve C
>
>