Page 152 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Mode
Microsoft Office Excel 2003 Programming Inside Out

Key is a string value representing the key combination to be assigned; see Table 6-3 for a list
of special key codes.
Procedure is a string value representing the name of the procedure to be invoked. If Procedure
is blank (that is, the parameter is set to the empty string by assigning it the value " "), the key
combination performs no action, even if Excel normally reacts to the key combination. If the
Procedure argument is omitted entirely (that is, there is nothing after the comma), the key
combination is reset to normal, default actions.

You can combine a key with one or more of the key modifiers (Shift, Ctrl, and/or Alt) to create
key combinations as needed. The following example runs a procedure when Alt+H is pressed:

Application.OnKey "%h", "MyProcedure"

The following example takes the key combination that normally opens the Excel Find and
Replace (Ctrl+H) dialog box and uses it to invoke a custom procedure:

Application.OnKey "^h", "MyProcedure"
This example stops any code from running when Ctrl+H is pressed.

Application.OnKey "^h", ""

This last example resets Ctrl+H to the default action in Excel (Find and Replace).
Application.OnKey "^h"

The most common usage of the OnKey method would be to set hot keys when a workbook is
opened or closed. This allows the workbook to provide hot keys that are specific to the work-
book and resets the keys to their default values when closed. The two following procedures,
when placed inside the Y2001ByMonth.xls file, assign the hot key of Alt+C to the CopySheet
procedure to add a new worksheet to the workbook:

Sub Auto_Open()
Application.OnKey "%c", "CopySheet"
End Sub

Sub Auto_Exit()
Application.OnKey "%c"
End Sub

Table 6-3. Key Codes
Key Key Code
Backspace {Backspace} or {BS}
Break {Break}
Caps Lock {CapsLock}
Clear {Clear}
Chapter 6

126