Page 298 - Excel Progamming Weekend Crash Course

r540629 ch21.qxd 9/2/03 9:35 AM Page 273

Session 21 — Advanced User Form Techniques 273

To work with the KeyCode argument in the event procedure, you usually use the con-
stants that are defined by VBA for each key. These constants take the form vbKeyXX where
XX identifies the key. For example, vbKeyA is the A key, vbKeyDown is the down key, and
vbKeyNumpad5 is the 5 key on the numeric keypad. You can find a complete list of the
KeyCode values in the VBA online help.
The Shift argument identifies which shift keys, if any, were down. In this context the
term shift keys refers to the Shift, Ctrl, and Alt keys. There are three constants you can use
for this purpose:

fmShiftMask refers to the Shift key.
fmCtrlMask refers to the Ctrl key.
fmAltMask refers to the Alt key.

If no shift key was down, the Shift argument is 0. If one of the keys was down, the
Shift argument is equal to the corresponding constant. If two or three of the shift keys
are down, the argument is the logical AND of the corresponding arguments. For example:
If Shift = fmShiftMask And fmCtrlMask Then
‘ Shift and Ctrl keys were down.
End If
It’s important to note that the KeyCode argument identifies the key that was pressed and
that’s all. For example, if KeyCode is equal to vbKey7, you know that the 7 key along the
top of the keyboard was pressed; however, only by examining the Shift argument can you
determine if the user was entering 7 or was pressing the Shift key at the same time in order
to enter an ampersand (&).
After checking the key, you can do two things with the code in the KeyDown event
procedure:
Set the KeyCode argument to 0, which cancels the keystroke.
Leave the KeyCode argument as is, which allows the keystroke to get through to the
control.

You learn how to use the KeyDown event procedure to restrict input to a text
box in Session 22.
Cross-Ref

Advanced Form Design
You have already learned the fundamentals of user form design in Session 19. These basics
serve you well for designing relatively simple forms, but at some point you want greater
control over the appearance of your forms. These techniques are covered in this section.

The Form Grid
You may have already noticed when placing or resizing controls on a form that you cannot
select just any position or size. Rather, the position and size of any control is constrained