Another change event question

[Steph]

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?

 

[Rob van Gelder]

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.

 

[Bob Phillips]

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)

 

[Paolo De Laurentiis]

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]

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]

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]

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]

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 De Laurentiis]

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

 

[Otto Moehrbach]

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

 

[Paolo De Laurentiis]

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

 

[Otto Moehrbach]

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]

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