OnKey method

The syntax for the OnKey method is:

Application.OnKey(Key, Procedure)

where Key is the key or key combination (written as a string) that will execute the macro and Procedure is the name of that macro.

Note that we can alter the normal behavior of Excel by assigning a key combination to the Key parameter that has a normal Excel response (such as Ctrl-S for save). If we assign an empty string to the Procedure parameter, then Excel will omit its normal response (so nothing will happen). If we omit the Procedure parameter, then Excel will return the key combination to its normal function.

To illustrate, the following code will disable the Ctrl-o key combination, which normally displays the Open dialog box:

Application.OnKey "*o",""

The following code returns the Ctrl-o key combination to its normal Excel function:

Application.OnKey "Ao"

The Key argument can specify a single key or any key combined with one or more of Alt, Ctrl, or Shift. Normal alphanumeric keys are denoted by themselves, as in "a," "A," "1." Table 16-3 shows how to enter special keys. For instance, the F2 key is denoted by "{F2}", and the Enter key is denoted either by "{ENTER}" or "~".

Table 16-3. Special Keys for the Key Parameter

Key

Code

Backspace

{BACKSPACE} or {BS}

Break

{BREAK}

Caps Lock

{CAPSLOCK}

Clear

{CLEAR}

Delete or Del

{DELETE} or {DEL}

Down Arrow

{DOWN}

End

{END}

Enter (numeric keypad)

{ENTER}

Enter

~ (tilde)

Esc

{ESCAPE} or {ESC}

Help

{HELP}

Home

{HOME}

Ins

{INSERT}

Left Arrow

{LEFT}

Num Lock

{NUMLOCK}

Page Down

{PGDN}

Page Up

{PGUP}

Return

{RETURN}

Right Arrow

{RIGHT}

Scroll Lock

{SCROLLLOCK}

Tab

{TAB}

Up Arrow

{UP}

F1 through F15

{F1} through {F15}

To combine keys with Shift, Ctrl, or Alt, use the following prefixes:

Shift

+ (plus sign)

Ctrl

A (caret)

Alt

% (percent sign)

For instance, to denote the Alt-F2 key combination, write "%{F2}". To denote Ctrl-Shift-Enter, write ,,A+{ENTER}".

In order to use one of the characters +, A, %, {, }, or ~ without having it interpreted as a special key, simply enclose the character in braces. For instance, to reassign the { key, we would assign the Key parameter to "{{}".

Was this article helpful?

0 0

Responses

  • MARTINA
    How to add application.onkey to string macro?
    1 year ago

Post a comment