retriveing old AND new value of a cell in code
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 |
retriveing old AND new value of a cell in code
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 |
retriveing old AND new value of a cell in code
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 -- Regards, Tom Ogilvy "E.J. van Wijngaarden" wrote in message ... 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 |
retriveing old AND new value of a cell in code
"Tom Ogilvy" schreef in bericht ... 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 -- Regards, Tom Ogilvy "E.J. van Wijngaarden" wrote in message ... 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 Hello Tom, Application.Undo is the trick I was looking for. Thanks! Ed |
All times are GMT +1. The time now is 08:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com