Microsoft Office Tutorials and References
In Depth Information
Worksheet_PivotTableUpdate Event
12.
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
Change event:
Dim OldVal As Double, NewVal As Double
13.
Define the NewVal variable first, because it is the number that was just entered into the cell:
NewVal = Target.Value
14.
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
15.
Execute Undo so the previous value is reestablished:
Application.Undo
16.
Define the OldVal variable, which is possible to do now that the previous value has been
restored:
OldVal = Target.Value
17.
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
were numbers:
Target.Value = OldVal + NewVal
18.
Enable events now that all the changes to the cell have been made:
Application.EnableEvents = True
19.
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,
Search JabSto ::




Custom Search