For a reason, you may wish to simply add a value in an excel cell and to have it automatically summed with the value, that was previously present in the cell. This can be valuable, if you are building some advanced Excel tool and in some cells you would like to add numbers to the cells. Or just to amaze someone how advanced your Excel knowledge skill is.
How the magic is done? We use two tricks here – the Worksheet_Change routine and the Application.Undo. The Application.Undo is a bit tricky, because it should be surrounded by Application.EnableEvents. The rest is pure calculation of the Target.Value before the undo and after it. Enjoy the code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim dCalculate As Double If Not IsNumeric(Target.Value) Then Exit Sub End If dCalculate = Target.Value Application.EnableEvents = False Application.Undo Target.Value = Target.Value + dCalculate Application.EnableEvents = True End Sub |
🙂