Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another change event question
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" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another change event question
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" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another change event question
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" ha scritto nel messaggio ... 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" wrote in message ... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another change event question
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" wrote in message ... 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" ha scritto nel messaggio ... 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" wrote in message ... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another change event question
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" ha scritto nel messaggio ... 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" wrote in message ... 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" ha scritto nel messaggio ... 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" wrote in message ... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another change event question
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" wrote in message ... 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" ha scritto nel messaggio ... 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" wrote in message ... 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" ha scritto nel messaggio ... 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" wrote in message ... 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another change event question
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" ha scritto nel messaggio ... 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" wrote in message ... 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" ha scritto nel messaggio ... 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" wrote in message ... 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" ha scritto nel messaggio ... 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" wrote in message ... 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another change event question
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" ha scritto nel messaggio ... 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" ha scritto nel messaggio ... 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" wrote in message ... 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" ha scritto nel messaggio ... 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" wrote in message ... 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" ha scritto nel messaggio ... 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" wrote in message ... 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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another change event question
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" wrote in message ... 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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another change event question
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" ha scritto nel messaggio ... 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" wrote in message ... 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? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another change event question
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" wrote in message ... 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" ha scritto nel messaggio ... 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" wrote in message ... 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? |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another change event question
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" ha scritto nel messaggio ... 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" wrote in message ... 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" ha scritto nel messaggio ... 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" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Quick VBA Worksheet Change Event or Selection Question: | Excel Worksheet Functions | |||
Sheet change event and list validation question | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming | |||
change event question | Excel Programming | |||
change event/after update event?? | Excel Programming |