Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Retriveing info from "child | Excel Discussion (Misc queries) | |||
when retriveing stock data, how do I get just the last price? | Excel Discussion (Misc queries) | |||
Retriveing a text from one sheet and insert to another | Excel Worksheet Functions |