Microsoft Office Tutorials and References
In Depth Information
Worksheet_PivotTableUpdate Event
‘never assume that will always happen because users do make mistakes.
‘Provide for the attempt at a non-numeric entry and disallow it.
If IsNumeric(Target.Value) = False Then
‘Disable events because you are about to undo the non-numeric value,
‘and Undo also triggers the Change event.
Application.EnableEvents = False
‘Execute the Undo so the non-numeric entry is deleted.
Application.Undo
‘Enable events again.
Application.EnableEvents = True
‘Remind the user with a Message Box that only numbers are allowed,
‘and exit the Change event procedure with the Exit Sub statement.
MsgBox “You entered a non-numeric value.”, _
vbExclamation, _
“Please: numbers only in column A!”
Exit Sub
End If
‘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 got 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
‘Define the NewVal variable first, as 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 to not
‘re-trigger the Change event while you are already in a Change event.
Application.EnableEvents = False
‘Execute Undo so the previous value is re-established.
Application.Undo
‘Define the OldVal variable which is possible to do now that
‘the previous value has been restored.
OldVal = Target.Value
‘Programmatically enter into the cell the sum of the old 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
‘Enable events now that all the changes to the cell have been made.
Application.EnableEvents = True
End Sub
20.
Press Alt+Q to return to the worksheet. Test the code by entering a series of numbers in any
single cell in column A other than cell A1.
To view the video that accompanies this lesson, please select Lesson 11, available
at the following website: www.wrox.com/go/excelvba .
Search JabSto ::




Custom Search