Previous value of cell before change
 

When a cell value is changed and the worksheet change event is
triggered - is there a way I can get the previous value in the cell
before it was changed?

sai

Previous value of cell before change
 

Hi
you could use a static variable to store the previous result. Try something
like
Private Sub Worksheet_Change(ByVal Target As Range)
Static old_value
If Target.Address = "$A$1" Then
MsgBox "Old value: " & old_value
old_value = Target.Value
End If
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany
"sai" schrieb im Newsbeitrag
ups.com...
When a cell value is changed and the worksheet change event is
triggered - is there a way I can get the previous value in the cell
before it was changed?

sai

Previous value of cell before change
 

Frank,

The problem is that it is just not one cell. I have a whole column (and
maybe multiple columns) of numbers for which I will need to know the
previous value. I do not want to carry the same data that is in the
spreadsheet in memory in an array.

The reason I need this is to be able to do a delta. I want to know the
incremental change made - based on which I want to update another cell.

When I use application.undo, the previous value is restored. Is there a way
I can access the undo object (?) to get the previous value?

sai

"Frank Kabel" wrote in message
...
Hi
you could use a static variable to store the previous result. Try
something
like
Private Sub Worksheet_Change(ByVal Target As Range)
Static old_value
If Target.Address = "$A$1" Then
MsgBox "Old value: " & old_value
old_value = Target.Value
End If
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany
"sai" schrieb im Newsbeitrag
ups.com...
When a cell value is changed and the worksheet change event is
triggered - is there a way I can get the previous value in the cell
before it was changed?

sai

Previous value of cell before change
 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldVal as Variant, NewVal as Variant
if target.count 1 then exit sub
Application.EnableEvents = False
newval = Target.Value
application.Undo
oldval = Target.Value
if isnumeric(OldVal) and isnumeric(NewVal) then
target.offset(0,1).Value = NewVal - OldVal
end if
Target.Value = NewVal
Application.EnableEvents = True
End Sub

You may want to limit the range in which this occurs.

--
Regards,
Tom Ogilvy


"sai" wrote in message
...
Frank,

The problem is that it is just not one cell. I have a whole column (and
maybe multiple columns) of numbers for which I will need to know the
previous value. I do not want to carry the same data that is in the
spreadsheet in memory in an array.

The reason I need this is to be able to do a delta. I want to know the
incremental change made - based on which I want to update another cell.

When I use application.undo, the previous value is restored. Is there a
way
I can access the undo object (?) to get the previous value?

sai

"Frank Kabel" wrote in message
...
Hi
you could use a static variable to store the previous result. Try
something
like
Private Sub Worksheet_Change(ByVal Target As Range)
Static old_value
If Target.Address = "$A$1" Then
MsgBox "Old value: " & old_value
old_value = Target.Value
End If
End Sub


--
Regards
Frank Kabel
Frankfurt, Germany
"sai" schrieb im Newsbeitrag
ups.com...
When a cell value is changed and the worksheet change event is
triggered - is there a way I can get the previous value in the cell
before it was changed?

sai