Microsoft Office Tutorials and References
In Depth Information
Now that all the reasonable safeguards have been met, declare two Double type variables:
one named OldVal for the numeric value that was in the cell before it was changed, and the
other variable named NewVal for the numeric value that was just entered that triggered this
Dim OldVal As Double, NewVal As Double
Define the NewVal variable first, because it is the number that was just entered into the cell:
NewVal = Target.Value
Undo the entry in order to display the old (preceding) value. Again, this requires that you
disable events in order not to re-trigger the Change event while you are already in a Change event:
Application.EnableEvents = False
Execute Undo so the previous value is reestablished:
Define the OldVal variable, which is possible to do now that the previous value has been
OldVal = Target.Value
Programmatically enter into the cell the sum of the previous value, plus the new last-entered
value, by referring to those two variables in an arithmetic equation just as you would if they
Target.Value = OldVal + NewVal
Enable events now that all the changes to the cell have been made:
Application.EnableEvents = True
When completed, the entire procedure will look like this, with comments that have been
added to explain each step:
Private Sub Worksheet_Change(ByVal Target As Range)
‘Allow for a header label to be placed in cell A1.
If Target.Address = “$A$1” Then Exit Sub
‘Only apply this effect to column A (column 1 in VBA-Speak).
‘At the same time, only allow one cell at a time to be changed.
If Target.Column <> 1 Or Target.Cells.Count > 1 Then Exit Sub
‘Pressing the Delete key triggers the Change event.
‘You might want to delete the cell’s contents and start with
‘an empty cell, so exit the Change event if the Delete key is pressed.
If IsEmpty(Target) Then Exit Sub
‘Even though a number is *supposed* to be entered into column A,