Execute A Procedure When You Press Keys

You can create a procedure that executes when you press a specific key or combination of keys. For example, you can change the built key combination of Ctrl+S for saving a workbook to display your own custom pop-up dialog box. To do this, you capture the OnKey event. If you specify a key combination that Excel already uses, your new definition overrides the Excel combination.

Unlike most other events, the OnKey event is not associated with a specific object. For this reason, you access this event you using the OnKey method that is associated with the

Application object.

The OnKey method has two different parameters. You use the Key parameter to specify the key combination, which you express as a string consisting of the combined keys you capture. You represent standard keys, such as a and 5, by simply typing the character for the key. You specify nonstandard keys, such as Delete and Insert, by placing the key name in brackets, such as {DELETE} or {INSERT}.

You must use the Procedure parameter to indicate the name of the procedure to execute at the specified time. Remember to enclose the procedure name in quotes.

Because the OnKey event is not associated with a specific object, you can place your procedure containing the method for accessing the event in any code module. Keep in mind that if you place the OnKey method procedure in a standard code window, you need to run the corresponding macro before the OnKey event code activates. You can place the OnKey method within the Workbook_Open procedure so that it loads as the workbook opens. See a section "Run a Procedure as a Workbook Opens" for more information.

EXECUTE A PROCEDURE WHEN YOU PRESS KEYS

EXECUTE A PROCEDURE WHEN YOU PRESS KEYS

AcceptLabelsInF AutoUpdateFreqO ChangeHistoryDiO ConflictResolutio 1 - xlU Datel904 False DisplayDrawingC -4104 EnableAutoReco True EnvelopeVisible False

'-n In the Projects window, double-click the ThisWorkbook object code.

■ The code module opens for the ThisWorkbook object.

L0 In the Workbook_Open procedure, type Application.OnKey "As", "CustomSave", replacing "~s" with a valid key combination string and "CustomSave" with the procedure to run.

Note: See the section "Run a Procedure as a Workbook Opens" for information on the Workbook_open procedure.

AUTOMATING PROCEDURES WITH EXCEL EVENTS

When specifying keys that do not display a character, such as Delete or Down Arrow, you stipulate the name of the key within braces, such as {Delete} or {Down}. For some specific keys, Excel provides special characters to represent the key when you combine them with other characters:

CHARACTER

REPRESENTS

+

SHIFT

A

CTRL

0/ %

ALT

ENTER

If you want to assign a particular key combination back to its original meaning in Excel, you omit the Procedure parameter:

TYPE THIS:

Application.OnKey "+^{LEFT}"

RESULT:

The custom key combination assignment is removed and Excel executes the default command for that key combination, if one exists.

To use one of these special characters in your key combination, enclose the character in braces. For example, to specify a procedure to execute when you press the precent sign you type the following code.

TYPE THIS:

Application.OnKey "{%}", "ExecutePercent"

RESULT:

Whenever the % key is pressed on the keyboard the ExecutePercent procedure executes.

□ Open the workbook in Excel.

■ The Workbook_Open subroutine activates the OnKey method and the specified procedure executes when you press the key combination.

Create a new subroutine with the same name as the procedure specified in step 2.

Note: See Chapter 3 for information on creating subroutines.

□ Type the VBA code to run when the subroutine executes.

0 Close Excel.

□ Open the workbook in Excel.

□ Press the custom key combination.

■ The Workbook_Open subroutine activates the OnKey method and the specified procedure executes when you press the key combination.

0 0

Post a comment