OldValue property
In Access there is a property called OldValue. It holds onto the value when
a field is changed. What I am looking for is something similar in Excel. I've written a Worksheet_Change event which will look for a value entered into a cell within a certain specified range to make sure the value hasn't already been entered elsewhere in the range. The only thing I need is a way to retain the OldValue in case I need to recall the value and nullify the change. Ideas? Thanks |
OldValue property
There is no OldValue property
In the change event you would use some code similar to this pseudocode. Dim OldValue as Variant Dim NewValue as Variant Applicaton.EnableEvents = False NewValue = Target.Value Application.Undo OldValue = Target.Value Target.value = NewValue Application.EnableEvents = True -- Regards, Tom Ogilvy "Lulu" wrote in message ... In Access there is a property called OldValue. It holds onto the value when a field is changed. What I am looking for is something similar in Excel. I've written a Worksheet_Change event which will look for a value entered into a cell within a certain specified range to make sure the value hasn't already been entered elsewhere in the range. The only thing I need is a way to retain the OldValue in case I need to recall the value and nullify the change. Ideas? Thanks |
OldValue property
Hi
For a start: Option Explicit Dim OldValue As Variant Private Sub Worksheet_SelectionChange(ByVal Target As Range) OldValue = Target(1).Formula End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target(1).Formula < OldValue Then _ MsgBox "Used to be " & CStr(OldValue) End Sub Problem is mass changes, as in "select a ton of cells and Delete" or "Paste" if a ton of cells are copied into the clipboard. There is no Right Way to deal with those events, you decide. But the Target variable, as you may know, is the range of all the affected cells. Target(1), as shown, works for me in most cases. HTH. Best wishes Harald "Lulu" skrev i melding ... In Access there is a property called OldValue. It holds onto the value when a field is changed. What I am looking for is something similar in Excel. I've written a Worksheet_Change event which will look for a value entered into a cell within a certain specified range to make sure the value hasn't already been entered elsewhere in the range. The only thing I need is a way to retain the OldValue in case I need to recall the value and nullify the change. Ideas? Thanks |
OldValue property
Thanks to Tom and Harald. After some experimentation, I ended up using a
slight variation of Harald's solution. They were both good. I had been dancing around the right answer all along. I had not realized the OldValue determined in the SelectionChange event would carry forward to the Worksheet_Change event without some kind of Call procedure. Thanks for that insight. "Harald Staff" wrote: Hi For a start: Option Explicit Dim OldValue As Variant Private Sub Worksheet_SelectionChange(ByVal Target As Range) OldValue = Target(1).Formula End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target(1).Formula < OldValue Then _ MsgBox "Used to be " & CStr(OldValue) End Sub Problem is mass changes, as in "select a ton of cells and Delete" or "Paste" if a ton of cells are copied into the clipboard. There is no Right Way to deal with those events, you decide. But the Target variable, as you may know, is the range of all the affected cells. Target(1), as shown, works for me in most cases. HTH. Best wishes Harald "Lulu" skrev i melding ... In Access there is a property called OldValue. It holds onto the value when a field is changed. What I am looking for is something similar in Excel. I've written a Worksheet_Change event which will look for a value entered into a cell within a certain specified range to make sure the value hasn't already been entered elsewhere in the range. The only thing I need is a way to retain the OldValue in case I need to recall the value and nullify the change. Ideas? Thanks |
All times are GMT +1. The time now is 08:38 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com