retriveing old AND new value of a cell in code

[E.J. van Wijngaarden]

Hello experts,

I have an Excel application that uses the Worksheet_Change event to automate
a stock of articles.
So when a new order is placed I read the number of articles that is entered
using Target.Value in Worksheet_Change and substract it from the total
stock.
This works OK for new entered articles.
But when I change the number of articles of an entry that has already be
filled in, it keeps substracting the full amount.
For example: I enter a new order with 14 articles. The number 14 is
substracted from the total stock.
Now I change 14 to 24. Then the number 24 is substracted from the total
stock.
Now the stock is incorrect!

I want to be able to substract the difference between the old and new value:
24-14=10.
BUT: I see no way to read the old value of the cell.
In Worksheet_Change using Target.Vale I see only the 24, because it shows
the new value.
There does not seem to be an event that makes it possible to read the value
BEFORE the change.

How can I solve this?

Thanks for your answer!

Ed van Wijngaarden

 

[arno]

Hi E.J.,

maybe you should add a macro "ChangeMyEnteredValue". You place the
cursor on a cell you want to change and run the macro, which will ADD
the amount that is in the cell to the stock value, then it will enter O
(zero) in that cell. I do not know if this will fire your event but it
does not matter as it will calculate with zero. Now you can enter your
new amount which will correctly fire your event.

arno

 

[Tom Ogilvy]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vValNew As Variant
Dim vValOld As Variant
On Error GoTo ErrHandler
If Target.Address = "$A$1" Then
vValNew = Target.Value
If IsNumeric(vValNew) And Target.Value <> "" Then
Application.EnableEvents = False
Application.Undo
vValOld = Target.Value
If Len(Trim(vValOld)) <> 0 Then
If IsNumeric(vValOld) Then
Target.Value = vValOld - vValNew
Else
Target.Value = vValNew
End If
Else
Target.Value = vValNew
End If
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

 

[Dory en Ed]

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vValNew As Variant
Dim vValOld As Variant
On Error GoTo ErrHandler
If Target.Address = "$A$1" Then
vValNew = Target.Value
If IsNumeric(vValNew) And Target.Value <> "" Then
Application.EnableEvents = False
Application.Undo
vValOld = Target.Value
If Len(Trim(vValOld)) <> 0 Then
If IsNumeric(vValOld) Then
Target.Value = vValOld - vValNew
Else
Target.Value = vValNew
End If
Else
Target.Value = vValNew
End If
End If
End If
ErrHandler:
Application.EnableEvents = True
End Sub

Hello Tom,

Application.Undo is the trick I was looking for.
Thanks!

Ed