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

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

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

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