Microsoft Office Tutorials and References
In Depth Information
Worksheet_PivotTableUpdate Event
4.
The event code will monitor column A but you will want the ability to enter some kind of
header label into cell A1. Begin the procedure by writing a line of code to exclude cell A1
from the Change event:
If Target.Address = “$A$1” Then Exit Sub
5.
Your next consideration is to limit the Change event to column A, to avoid imposing the
Change event onto the entire worksheet. Also, you will want the Change event to be in effect
for only one cell at a time in column A. One statement can handle both considerations:
If Target.Column <> 1 Or Target.Cells.Count > 1 Then Exit Sub
Note that column A is the first (leftmost) column in the worksheet grid and is easily referred
to in VBA as Columns(1) .
6.
Pressing the Delete key triggers the Change event. You might want to delete a cell’s contents
and start entering a new set of numbers in an empty cell, so allow yourself the luxury of
exiting the Change event if the Delete key is pressed:
If IsEmpty(Target) Then Exit Sub
7.
Even though a number is supposed to be entered into column A, never assume that it will
always happen that way, because people make mistakes. Provide for the attempt at a
nonnumeric entry and disallow it:
If IsNumeric(Target.Value) = False Then
8.
Disable events because you are about to undo the non-numeric value; the Undo command
also triggers the Change event:
Application.EnableEvents = False
9.
Execute the Undo action so the non-numeric entry is deleted:
Application.Undo
10.
Enable events again:
Application.EnableEvents = True
11.
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
Search JabSto ::




Custom Search