Another change event question

Can I somehow capture what the cell value was before it was changed?

I know I can capture the cell changed and the changed value in the
following:
MsgBox "You changed: " & Target.Address & " to " & Target.Value

I would love it to tell me You changed A1 from 15 to 25

Possible?

There is no event for BeforeChange, unfortunately.

You must store the value separately as the cell changes so you can refer to
the value at next change.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Steph" <[email protected]> wrote in message
news:[email protected]...
> Can I somehow capture what the cell value was before it was changed?
>
> I know I can capture the cell changed and the changed value in the
> following:
> MsgBox "You changed: " & Target.Address & " to " & Target.Value
>
> I would love it to tell me You changed A1 from 15 to 25
>
> Possible?
>
>

Oh yes! Here is an example

Option Explicit

Dim oldVal

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
MsgBox "You changed: " & .Address(False, False) & " from " & oldVal
& _
" to " & .Value
oldVal = .Value
End With
ws_exit:
Application.EnableEvents = True
End Sub


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


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Steph" <[email protected]> wrote in message
news:[email protected]...
> Can I somehow capture what the cell value was before it was changed?
>
> I know I can capture the cell changed and the changed value in the
> following:
> MsgBox "You changed: " & Target.Address & " to " & Target.Value
>
> I would love it to tell me You changed A1 from 15 to 25
>
> Possible?
>
>

Hi Bob,

your way is exactly what I'm using to track changes to an excel worksheet.
Actually, you can have it non "worksheet related", but "application
related", just writing a class module of the Application object.

Write this in a class module:

Public WithEvents App As Application
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
'store previous value here
End Sub
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'read changed value here
End Sub

Then set a variable in a module to the class object:
dim x as ClassModuleName
Set x.App = Application

Then you're done.
However, this is not perfect... please have a look to my posting:
"Posting again. Please Help! How to "track changes" using VBA"

Paolo


"Bob Phillips" <[email protected]> ha scritto nel messaggio
news:[email protected]...
> Oh yes! Here is an example
>
> Option Explicit
>
> Dim oldVal
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> With Target
> MsgBox "You changed: " & .Address(False, False) & " from " & oldVal
> & _
> " to " & .Value
> oldVal = .Value
> End With
> ws_exit:
> Application.EnableEvents = True
> End Sub
>
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> oldVal = Target.Value
> End Sub
>
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Steph" <[email protected]> wrote in message
> news:[email protected]...
>> Can I somehow capture what the cell value was before it was changed?
>>
>> I know I can capture the cell changed and the changed value in the
>> following:
>> MsgBox "You changed: " & Target.Address & " to " & Target.Value
>>
>> I would love it to tell me You changed A1 from 15 to 25
>>
>> Possible?
>>
>>
>
>

Paolo,

You can have it worksheet, workbook or even application related, but it
depends upon what is wanted.

Actually, you don't need to create a class module, put it in the
ThisWorkbook module, which is a class module, and you don't need to
instantiate the class (ThisWorkbook is implicitly instantiated).

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paolo De Laurentiis" <[email protected]> wrote in message
news:[email protected]...
> Hi Bob,
>
> your way is exactly what I'm using to track changes to an excel worksheet.
> Actually, you can have it non "worksheet related", but "application
> related", just writing a class module of the Application object.
>
> Write this in a class module:
>
> Public WithEvents App As Application
> Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
> Range)
> 'store previous value here
> End Sub
> Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> 'read changed value here
> End Sub
>
> Then set a variable in a module to the class object:
> dim x as ClassModuleName
> Set x.App = Application
>
> Then you're done.
> However, this is not perfect... please have a look to my posting:
> "Posting again. Please Help! How to "track changes" using VBA"
>
> Paolo
>
>
> "Bob Phillips" <[email protected]> ha scritto nel
messaggio
> news:[email protected]...
> > Oh yes! Here is an example
> >
> > Option Explicit
> >
> > Dim oldVal
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > With Target
> > MsgBox "You changed: " & .Address(False, False) & " from " &
oldVal
> > & _
> > " to " & .Value
> > oldVal = .Value
> > End With
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub
> >
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > oldVal = Target.Value
> > End Sub
> >
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Steph" <[email protected]> wrote in message
> > news:[email protected]...
> >> Can I somehow capture what the cell value was before it was changed?
> >>
> >> I know I can capture the cell changed and the changed value in the
> >> following:
> >> MsgBox "You changed: " & Target.Address & " to " & Target.Value
> >>
> >> I would love it to tell me You changed A1 from 15 to 25
> >>
> >> Possible?
> >>
> >>
> >
> >
>
>

Thanks Bob.
In my case, I really want to keep track of all changes happening in every
workbook, so I suppose I need it at application level, am I right?
Do you have any suggestion on my previous posting ("Posting again. Please
Help! How to "track changes" using VBA")

Paolo

"Bob Phillips" <[email protected]> ha scritto nel messaggio
news:[email protected]...
> Paolo,
>
> You can have it worksheet, workbook or even application related, but it
> depends upon what is wanted.
>
> Actually, you don't need to create a class module, put it in the
> ThisWorkbook module, which is a class module, and you don't need to
> instantiate the class (ThisWorkbook is implicitly instantiated).
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Paolo De Laurentiis" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Bob,
>>
>> your way is exactly what I'm using to track changes to an excel
>> worksheet.
>> Actually, you can have it non "worksheet related", but "application
>> related", just writing a class module of the Application object.
>>
>> Write this in a class module:
>>
>> Public WithEvents App As Application
>> Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
>> Range)
>> 'store previous value here
>> End Sub
>> Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
>> 'read changed value here
>> End Sub
>>
>> Then set a variable in a module to the class object:
>> dim x as ClassModuleName
>> Set x.App = Application
>>
>> Then you're done.
>> However, this is not perfect... please have a look to my posting:
>> "Posting again. Please Help! How to "track changes" using VBA"
>>
>> Paolo
>>
>>
>> "Bob Phillips" <[email protected]> ha scritto nel
> messaggio
>> news:[email protected]...
>> > Oh yes! Here is an example
>> >
>> > Option Explicit
>> >
>> > Dim oldVal
>> >
>> > Private Sub Worksheet_Change(ByVal Target As Range)
>> >
>> > On Error GoTo ws_exit:
>> > Application.EnableEvents = False
>> > With Target
>> > MsgBox "You changed: " & .Address(False, False) & " from " &
> oldVal
>> > & _
>> > " to " & .Value
>> > oldVal = .Value
>> > End With
>> > ws_exit:
>> > Application.EnableEvents = True
>> > End Sub
>> >
>> >
>> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> > oldVal = Target.Value
>> > End Sub
>> >
>> >
>> > 'This is worksheet event code, which means that it needs to be
>> > 'placed in the appropriate worksheet code module, not a standard
>> > 'code module. To do this, right-click on the sheet tab, select
>> > 'the View Code option from the menu, and paste the code in.
>> >
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Steph" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Can I somehow capture what the cell value was before it was changed?
>> >>
>> >> I know I can capture the cell changed and the changed value in the
>> >> following:
>> >> MsgBox "You changed: " & Target.Address & " to " & Target.Value
>> >>
>> >> I would love it to tell me You changed A1 from 15 to 25
>> >>
>> >> Possible?
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Paolo,

Just spotted a flaw in the Application approach.

Say that Sheet1!A1 has the value "ABC" and Sheet2!A1 has the value "XYZ". If
you select Sheet1, select A1, the old value gets stored as "ABC". Then
select Sheet2, and without any further selection, change A1 to "123". This
code thinks that it has changed from "ABC" to "123", not from "XYZ".

I think you can overcome this by storing the value on SheetActivate as well.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Paolo De Laurentiis" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob.
> In my case, I really want to keep track of all changes happening in every
> workbook, so I suppose I need it at application level, am I right?
> Do you have any suggestion on my previous posting ("Posting again. Please
> Help! How to "track changes" using VBA")
>
> Paolo
>
> "Bob Phillips" <[email protected]> ha scritto nel
messaggio
> news:[email protected]...
> > Paolo,
> >
> > You can have it worksheet, workbook or even application related, but it
> > depends upon what is wanted.
> >
> > Actually, you don't need to create a class module, put it in the
> > ThisWorkbook module, which is a class module, and you don't need to
> > instantiate the class (ThisWorkbook is implicitly instantiated).
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "Paolo De Laurentiis" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi Bob,
> >>
> >> your way is exactly what I'm using to track changes to an excel
> >> worksheet.
> >> Actually, you can have it non "worksheet related", but "application
> >> related", just writing a class module of the Application object.
> >>
> >> Write this in a class module:
> >>
> >> Public WithEvents App As Application
> >> Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As
> >> Range)
> >> 'store previous value here
> >> End Sub
> >> Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
> >> 'read changed value here
> >> End Sub
> >>
> >> Then set a variable in a module to the class object:
> >> dim x as ClassModuleName
> >> Set x.App = Application
> >>
> >> Then you're done.
> >> However, this is not perfect... please have a look to my posting:
> >> "Posting again. Please Help! How to "track changes" using VBA"
> >>
> >> Paolo
> >>
> >>
> >> "Bob Phillips" <[email protected]> ha scritto nel
> > messaggio
> >> news:[email protected]...
> >> > Oh yes! Here is an example
> >> >
> >> > Option Explicit
> >> >
> >> > Dim oldVal
> >> >
> >> > Private Sub Worksheet_Change(ByVal Target As Range)
> >> >
> >> > On Error GoTo ws_exit:
> >> > Application.EnableEvents = False
> >> > With Target
> >> > MsgBox "You changed: " & .Address(False, False) & " from " &
> > oldVal
> >> > & _
> >> > " to " & .Value
> >> > oldVal = .Value
> >> > End With
> >> > ws_exit:
> >> > Application.EnableEvents = True
> >> > End Sub
> >> >
> >> >
> >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >> > oldVal = Target.Value
> >> > End Sub
> >> >
> >> >
> >> > 'This is worksheet event code, which means that it needs to be
> >> > 'placed in the appropriate worksheet code module, not a standard
> >> > 'code module. To do this, right-click on the sheet tab, select
> >> > 'the View Code option from the menu, and paste the code in.
> >> >
> >> >
> >> > --
> >> >
> >> > HTH
> >> >
> >> > RP
> >> > (remove nothere from the email address if mailing direct)
> >> >
> >> >
> >> > "Steph" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Can I somehow capture what the cell value was before it was changed?
> >> >>
> >> >> I know I can capture the cell changed and the changed value in the
> >> >> following:
> >> >> MsgBox "You changed: " & Target.Address & " to " & Target.Value
> >> >>
> >> >> I would love it to tell me You changed A1 from 15 to 25
> >> >>
> >> >> Possible?
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

You're right, Bob!
In facts, I store previous values in the following event procedure (at
application level):
SheetSelectionChange
WorksheetActivate
WorkbookActivate

then control changes just in:
SheetChange

Paolo

--
Paolo
Milan, Italy
NOTE: remove QUESTONO from my email address for direct emailing
"Bob Phillips" <[email protected]> ha scritto nel messaggio
news:[email protected]...
> Paolo,
>
> Just spotted a flaw in the Application approach.
>
> Say that Sheet1!A1 has the value "ABC" and Sheet2!A1 has the value "XYZ".
> If
> you select Sheet1, select A1, the old value gets stored as "ABC". Then
> select Sheet2, and without any further selection, change A1 to "123". This
> code thinks that it has changed from "ABC" to "123", not from "XYZ".
>
> I think you can overcome this by storing the value on SheetActivate as
> well.
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "Paolo De Laurentiis" <[email protected]> wrote in message
> news:[email protected]...
>> Thanks Bob.
>> In my case, I really want to keep track of all changes happening in every
>> workbook, so I suppose I need it at application level, am I right?
>> Do you have any suggestion on my previous posting ("Posting again. Please
>> Help! How to "track changes" using VBA")
>>
>> Paolo
>>
>> "Bob Phillips" <[email protected]> ha scritto nel
> messaggio
>> news:[email protected]...
>> > Paolo,
>> >
>> > You can have it worksheet, workbook or even application related, but it
>> > depends upon what is wanted.
>> >
>> > Actually, you don't need to create a class module, put it in the
>> > ThisWorkbook module, which is a class module, and you don't need to
>> > instantiate the class (ThisWorkbook is implicitly instantiated).
>> >
>> > --
>> >
>> > HTH
>> >
>> > RP
>> > (remove nothere from the email address if mailing direct)
>> >
>> >
>> > "Paolo De Laurentiis" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi Bob,
>> >>
>> >> your way is exactly what I'm using to track changes to an excel
>> >> worksheet.
>> >> Actually, you can have it non "worksheet related", but "application
>> >> related", just writing a class module of the Application object.
>> >>
>> >> Write this in a class module:
>> >>
>> >> Public WithEvents App As Application
>> >> Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target
> As
>> >> Range)
>> >> 'store previous value here
>> >> End Sub
>> >> Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
>> >> 'read changed value here
>> >> End Sub
>> >>
>> >> Then set a variable in a module to the class object:
>> >> dim x as ClassModuleName
>> >> Set x.App = Application
>> >>
>> >> Then you're done.
>> >> However, this is not perfect... please have a look to my posting:
>> >> "Posting again. Please Help! How to "track changes" using VBA"
>> >>
>> >> Paolo
>> >>
>> >>
>> >> "Bob Phillips" <[email protected]> ha scritto nel
>> > messaggio
>> >> news:[email protected]...
>> >> > Oh yes! Here is an example
>> >> >
>> >> > Option Explicit
>> >> >
>> >> > Dim oldVal
>> >> >
>> >> > Private Sub Worksheet_Change(ByVal Target As Range)
>> >> >
>> >> > On Error GoTo ws_exit:
>> >> > Application.EnableEvents = False
>> >> > With Target
>> >> > MsgBox "You changed: " & .Address(False, False) & " from " &
>> > oldVal
>> >> > & _
>> >> > " to " & .Value
>> >> > oldVal = .Value
>> >> > End With
>> >> > ws_exit:
>> >> > Application.EnableEvents = True
>> >> > End Sub
>> >> >
>> >> >
>> >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> >> > oldVal = Target.Value
>> >> > End Sub
>> >> >
>> >> >
>> >> > 'This is worksheet event code, which means that it needs to be
>> >> > 'placed in the appropriate worksheet code module, not a standard
>> >> > 'code module. To do this, right-click on the sheet tab, select
>> >> > 'the View Code option from the menu, and paste the code in.
>> >> >
>> >> >
>> >> > --
>> >> >
>> >> > HTH
>> >> >
>> >> > RP
>> >> > (remove nothere from the email address if mailing direct)
>> >> >
>> >> >
>> >> > "Steph" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> Can I somehow capture what the cell value was before it was
>> >> >> changed?
>> >> >>
>> >> >> I know I can capture the cell changed and the changed value in the
>> >> >> following:
>> >> >> MsgBox "You changed: " & Target.Address & " to " & Target.Value
>> >> >>
>> >> >> I would love it to tell me You changed A1 from 15 to 25
>> >> >>
>> >> >> Possible?
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Here are the exact declaration inside the Class Module

Public WithEvents App As Application
Private Sub App_SheetActivate(ByVal Sh As Object)
' store previous values
End Sub
Private Sub App_WorkbookActivate(ByVal Wb As Workbook)
' store previous values
End Sub
Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
' store previous values
End Sub
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'check changes
End Sub

--
Paolo
Milan, Italy
NOTE: remove QUESTONO from my email address for direct emailing
"Paolo De Laurentiis" <[email protected]> ha scritto nel messaggio
news:[email protected]...
> You're right, Bob!
> In facts, I store previous values in the following event procedure (at
> application level):
> SheetSelectionChange
> WorksheetActivate
> WorkbookActivate
>
> then control changes just in:
> SheetChange
>
> Paolo
>
> --
> Paolo
> Milan, Italy
> NOTE: remove QUESTONO from my email address for direct emailing
> "Bob Phillips" <[email protected]> ha scritto nel
> messaggio news:[email protected]...
>> Paolo,
>>
>> Just spotted a flaw in the Application approach.
>>
>> Say that Sheet1!A1 has the value "ABC" and Sheet2!A1 has the value "XYZ".
>> If
>> you select Sheet1, select A1, the old value gets stored as "ABC". Then
>> select Sheet2, and without any further selection, change A1 to "123".
>> This
>> code thinks that it has changed from "ABC" to "123", not from "XYZ".
>>
>> I think you can overcome this by storing the value on SheetActivate as
>> well.
>>
>> --
>>
>> HTH
>>
>> RP
>> (remove nothere from the email address if mailing direct)
>>
>>
>> "Paolo De Laurentiis" <[email protected]> wrote in message
>> news:[email protected]...
>>> Thanks Bob.
>>> In my case, I really want to keep track of all changes happening in
>>> every
>>> workbook, so I suppose I need it at application level, am I right?
>>> Do you have any suggestion on my previous posting ("Posting again.
>>> Please
>>> Help! How to "track changes" using VBA")
>>>
>>> Paolo
>>>
>>> "Bob Phillips" <[email protected]> ha scritto nel
>> messaggio
>>> news:[email protected]...
>>> > Paolo,
>>> >
>>> > You can have it worksheet, workbook or even application related, but
>>> > it
>>> > depends upon what is wanted.
>>> >
>>> > Actually, you don't need to create a class module, put it in the
>>> > ThisWorkbook module, which is a class module, and you don't need to
>>> > instantiate the class (ThisWorkbook is implicitly instantiated).
>>> >
>>> > --
>>> >
>>> > HTH
>>> >
>>> > RP
>>> > (remove nothere from the email address if mailing direct)
>>> >
>>> >
>>> > "Paolo De Laurentiis" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> Hi Bob,
>>> >>
>>> >> your way is exactly what I'm using to track changes to an excel
>>> >> worksheet.
>>> >> Actually, you can have it non "worksheet related", but "application
>>> >> related", just writing a class module of the Application object.
>>> >>
>>> >> Write this in a class module:
>>> >>
>>> >> Public WithEvents App As Application
>>> >> Private Sub App_SheetSelectionChange(ByVal Sh As Object, ByVal Target
>> As
>>> >> Range)
>>> >> 'store previous value here
>>> >> End Sub
>>> >> Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As
>>> >> Range)
>>> >> 'read changed value here
>>> >> End Sub
>>> >>
>>> >> Then set a variable in a module to the class object:
>>> >> dim x as ClassModuleName
>>> >> Set x.App = Application
>>> >>
>>> >> Then you're done.
>>> >> However, this is not perfect... please have a look to my posting:
>>> >> "Posting again. Please Help! How to "track changes" using VBA"
>>> >>
>>> >> Paolo
>>> >>
>>> >>
>>> >> "Bob Phillips" <[email protected]> ha scritto nel
>>> > messaggio
>>> >> news:[email protected]...
>>> >> > Oh yes! Here is an example
>>> >> >
>>> >> > Option Explicit
>>> >> >
>>> >> > Dim oldVal
>>> >> >
>>> >> > Private Sub Worksheet_Change(ByVal Target As Range)
>>> >> >
>>> >> > On Error GoTo ws_exit:
>>> >> > Application.EnableEvents = False
>>> >> > With Target
>>> >> > MsgBox "You changed: " & .Address(False, False) & " from " &
>>> > oldVal
>>> >> > & _
>>> >> > " to " & .Value
>>> >> > oldVal = .Value
>>> >> > End With
>>> >> > ws_exit:
>>> >> > Application.EnableEvents = True
>>> >> > End Sub
>>> >> >
>>> >> >
>>> >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>>> >> > oldVal = Target.Value
>>> >> > End Sub
>>> >> >
>>> >> >
>>> >> > 'This is worksheet event code, which means that it needs to be
>>> >> > 'placed in the appropriate worksheet code module, not a standard
>>> >> > 'code module. To do this, right-click on the sheet tab, select
>>> >> > 'the View Code option from the menu, and paste the code in.
>>> >> >
>>> >> >
>>> >> > --
>>> >> >
>>> >> > HTH
>>> >> >
>>> >> > RP
>>> >> > (remove nothere from the email address if mailing direct)
>>> >> >
>>> >> >
>>> >> > "Steph" <[email protected]> wrote in message
>>> >> > news:[email protected]...
>>> >> >> Can I somehow capture what the cell value was before it was
>>> >> >> changed?
>>> >> >>
>>> >> >> I know I can capture the cell changed and the changed value in the
>>> >> >> following:
>>> >> >> MsgBox "You changed: " & Target.Address & " to " & Target.Value
>>> >> >>
>>> >> >> I would love it to tell me You changed A1 from 15 to 25
>>> >> >>
>>> >> >> Possible?
>>> >> >>
>>> >> >>
>>> >> >
>>> >> >
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>

Steph
If the change you are wanting to trap was done manually, you can set the
current (new value) value to a variable, then issue the Undo command and set
the old value to another variable. Then you have both values. If the
change was done by code, then you have to somehow capture the old value
before the change is done. HTH Otto
"Steph" <[email protected]> wrote in message
news:[email protected]...
> Can I somehow capture what the cell value was before it was changed?
>
> I know I can capture the cell changed and the changed value in the
> following:
> MsgBox "You changed: " & Target.Address & " to " & Target.Value
>
> I would love it to tell me You changed A1 from 15 to 25
>
> Possible?
>
>

Otto,
is this working for you (referring to manually done changes)?
I tried also this, but when the change event happen, I store the new values,
call the Undo, read the previous values, but then I'm unable to call a
Repeat to return to the situation after the changes.
In facts, the code I'm using causes a strange effect since I cannot Repeat
any action even using the standard Excel button: you see the application
repeating the action, but then the Undo is applied by the macro while the
repeat is not.

Here is the code I'm using.

Application.EnableEvents = False
Call StoreValues(True, True, Wb, Ws, Rng) 'read values after change
Application.Undo
Call StoreValues(False, True, Wb, Ws, Rng) 'read values before change
Application.Repeat
Application.EnableEvents = True

I'm thinking that I can simulate a Repeat writing via macro the new values
into the cells, since I've stored them, but I'm worried about formats and
comments and whatever the user can have changed in the cells, apart from the
values, with just one single copy and paste operation from the GUI.
Do you think I can use something like:

dim R as Range
set R= Rng 'where Rng is the range where the change happened, passed
by the SheetChange event
Application.EnableEvents = False
Call StoreValues(True, True, Wb, Ws, Rng) 'read values after change
Application.Undo
Call StoreValues(False, True, Wb, Ws, Rng) 'read values before change
set Rng = R 'so meaning that everything is copied to the worksheet
range: values, formatting, comments, names, etc.
'Application.Repeat
Application.EnableEvents = True

Thanks,
Paolo

--
Paolo
Milan, Italy
NOTE: remove QUESTONO from my email address for direct emailing

"Otto Moehrbach" <[email protected]> ha scritto nel messaggio
news:[email protected]...
> Steph
> If the change you are wanting to trap was done manually, you can set
> the current (new value) value to a variable, then issue the Undo command
> and set the old value to another variable. Then you have both values. If
> the change was done by code, then you have to somehow capture the old
> value before the change is done. HTH Otto
> "Steph" <[email protected]> wrote in message
> news:[email protected]...
>> Can I somehow capture what the cell value was before it was changed?
>>
>> I know I can capture the cell changed and the changed value in the
>> following:
>> MsgBox "You changed: " & Target.Address & " to " & Target.Value
>>
>> I would love it to tell me You changed A1 from 15 to 25
>>
>> Possible?
>>
>>
>
>

Paolo
Yes, I've used this many times before. No, you cannot reverse the Undo.
The code is something like this:
(Say the cell is A1)
Dim NewValue as Variant
Dim OldValue as Variant
NewValue = Range("A1").Value
Application.Undo
OldValue=Range("A1").Value
'Now you do what you want with your data, then put either OldValue or
NewValue into A1.
'Bracket some of this code with the EnableEvents code as needed. HTH
Otto
"Paolo De Laurentiis" <[email protected]> wrote in message
news:[email protected]...
> Otto,
> is this working for you (referring to manually done changes)?
> I tried also this, but when the change event happen, I store the new
> values, call the Undo, read the previous values, but then I'm unable to
> call a Repeat to return to the situation after the changes.
> In facts, the code I'm using causes a strange effect since I cannot Repeat
> any action even using the standard Excel button: you see the application
> repeating the action, but then the Undo is applied by the macro while the
> repeat is not.
>
> Here is the code I'm using.
>
> Application.EnableEvents = False
> Call StoreValues(True, True, Wb, Ws, Rng) 'read values after change
> Application.Undo
> Call StoreValues(False, True, Wb, Ws, Rng) 'read values before change
> Application.Repeat
> Application.EnableEvents = True
>
> I'm thinking that I can simulate a Repeat writing via macro the new values
> into the cells, since I've stored them, but I'm worried about formats and
> comments and whatever the user can have changed in the cells, apart from
> the values, with just one single copy and paste operation from the GUI.
> Do you think I can use something like:
>
> dim R as Range
> set R= Rng 'where Rng is the range where the change happened, passed
> by the SheetChange event
> Application.EnableEvents = False
> Call StoreValues(True, True, Wb, Ws, Rng) 'read values after change
> Application.Undo
> Call StoreValues(False, True, Wb, Ws, Rng) 'read values before change
> set Rng = R 'so meaning that everything is copied to the worksheet
> range: values, formatting, comments, names, etc.
> 'Application.Repeat
> Application.EnableEvents = True
>
> Thanks,
> Paolo
>
> --
> Paolo
> Milan, Italy
> NOTE: remove QUESTONO from my email address for direct emailing
>
> "Otto Moehrbach" <[email protected]> ha scritto nel messaggio
> news:[email protected]...
>> Steph
>> If the change you are wanting to trap was done manually, you can set
>> the current (new value) value to a variable, then issue the Undo command
>> and set the old value to another variable. Then you have both values.
>> If the change was done by code, then you have to somehow capture the old
>> value before the change is done. HTH Otto
>> "Steph" <[email protected]> wrote in message
>> news:[email protected]...
>>> Can I somehow capture what the cell value was before it was changed?
>>>
>>> I know I can capture the cell changed and the changed value in the
>>> following:
>>> MsgBox "You changed: " & Target.Address & " to " & Target.Value
>>>
>>> I would love it to tell me You changed A1 from 15 to 25
>>>
>>> Possible?
>>>
>>>
>>
>>
>
>

Thnaks Otto,

this is clear, but what if the user changed the value of the cell "A1" by
copying and pasting into it the cell "B2", which, in facts, contained not
only a value, but also a comment, some borders, a different number format
and a different font color and type.
Using your code, I'm going to loose everything of that apart the value.
Is there a simple way to store everything of a cell in a variable so that I
can easily re-apply everything from the variable to the cell?
Something like here below, which, I tried, unfortunately put in A2 just the
value of C2 and not also formats, comments, etc.
Dim r As Range
Set r = ActiveSheet.Range("C2")
ActiveSheet.Range("a2") = r

Paolo

--
Paolo
Milan, Italy
NOTE: remove QUESTONO from my email address for direct emailing
"Otto Moehrbach" <[email protected]> ha scritto nel messaggio
news:[email protected]...
> Paolo
> Yes, I've used this many times before. No, you cannot reverse the
> Undo. The code is something like this:
> (Say the cell is A1)
> Dim NewValue as Variant
> Dim OldValue as Variant
> NewValue = Range("A1").Value
> Application.Undo
> OldValue=Range("A1").Value
> 'Now you do what you want with your data, then put either OldValue or
> NewValue into A1.
> 'Bracket some of this code with the EnableEvents code as needed. HTH
> Otto
> "Paolo De Laurentiis" <[email protected]> wrote in message
> news:[email protected]...
>> Otto,
>> is this working for you (referring to manually done changes)?
>> I tried also this, but when the change event happen, I store the new
>> values, call the Undo, read the previous values, but then I'm unable to
>> call a Repeat to return to the situation after the changes.
>> In facts, the code I'm using causes a strange effect since I cannot
>> Repeat
>> any action even using the standard Excel button: you see the application
>> repeating the action, but then the Undo is applied by the macro while the
>> repeat is not.
>>
>> Here is the code I'm using.
>>
>> Application.EnableEvents = False
>> Call StoreValues(True, True, Wb, Ws, Rng) 'read values after change
>> Application.Undo
>> Call StoreValues(False, True, Wb, Ws, Rng) 'read values before change
>> Application.Repeat
>> Application.EnableEvents = True
>>
>> I'm thinking that I can simulate a Repeat writing via macro the new
>> values
>> into the cells, since I've stored them, but I'm worried about formats and
>> comments and whatever the user can have changed in the cells, apart from
>> the values, with just one single copy and paste operation from the GUI.
>> Do you think I can use something like:
>>
>> dim R as Range
>> set R= Rng 'where Rng is the range where the change happened,
>> passed
>> by the SheetChange event
>> Application.EnableEvents = False
>> Call StoreValues(True, True, Wb, Ws, Rng) 'read values after change
>> Application.Undo
>> Call StoreValues(False, True, Wb, Ws, Rng) 'read values before change
>> set Rng = R 'so meaning that everything is copied to the worksheet
>> range: values, formatting, comments, names, etc.
>> 'Application.Repeat
>> Application.EnableEvents = True
>>
>> Thanks,
>> Paolo
>>
>> --
>> Paolo
>> Milan, Italy
>> NOTE: remove QUESTONO from my email address for direct emailing
>>
>> "Otto Moehrbach" <[email protected]> ha scritto nel messaggio
>> news:[email protected]...
>>> Steph
>>> If the change you are wanting to trap was done manually, you can set
>>> the current (new value) value to a variable, then issue the Undo command
>>> and set the old value to another variable. Then you have both values.
>>> If the change was done by code, then you have to somehow capture the old
>>> value before the change is done. HTH Otto
>>> "Steph" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> Can I somehow capture what the cell value was before it was changed?
>>>>
>>>> I know I can capture the cell changed and the changed value in the
>>>> following:
>>>> MsgBox "You changed: " & Target.Address & " to " & Target.Value
>>>>
>>>> I would love it to tell me You changed A1 from 15 to 25
>>>>
>>>> Possible?
>>>>
>>>>
>>>
>>>
>>
>>
>
>
>