Excel

Run a macro when a particular key pressed using OnKey event

The OnKey event runs a specified procedure when a particular key (or any key combination of Ctrl, Shift, and Alt with another key) is pressed. You can also use this event to disable the existing or default key combinations.

Syntax: Application.OnKey (Key, Procedure)


The Application.OnKey procedure has two arguments:

  • Key
    A string indicating the key to be pressed. Each key is represented by one or more characters, such as a for the character a, {BACKSPACE} for the backspace key, or {DOWN} for for the down-arrow key.
  • Procedure
    A string indicating the name of the procedure to be run whenever the keystroke or key combination described in the Key parameter is pressed.

The following example uses the Application.OnKey method to set up an OnKey event. This event reassigns the Page-Down {PGDN} and Page-Up {PGUP} keys. After the EnableSlowScrolling procedure is executed, pressing Page-Down key executes the pgDown procedure which moves the cursor down one row, and pressing the Page-Up key executes the pgUp procedure moves the cursor up one row:

Option Explicit

Sub EnableSlowScrolling()
 Application.OnKey Key:="{PGDN}", Procedure:="pgDown"
 Application.OnKey Key:="{PGUP}", Procedure:="pgUp"
End Sub

Private Sub pgDown()
 ActiveWindow.SmallScroll down:=1
End Sub

Private Sub pgUp()
 ActiveWindow.SmallScroll up:=1
End Sub

Cancel or reset the OnKey events

By executing the following procedure, you cancel the OnKey events and return these keys (Page-Down and Page-UP) to their normal functionality:

Sub ResetScrolling
 Application.OnKey Key:="{PGDN}"
 Application.OnKey Key:="{PGUP}"
End Sub

Disable the default behavior of a key

Setting an empty string as the second argument for the Application.OnKey method, causes Excel to simply ignore the keystroke and do nothing at all. For example, the following instruction tells Excel to ignore the Page-Down and Page-Up keystrokes:

Sub DisableScrolling()
 Application.OnKey Key:="{PGDN}", Procedure:=""
 Application.OnKey Key:="{PGUP}", Procedure:=""
End Sub

Combine keys with Shift, Ctrl, Alt

To specify a key combined with another key(s), use:

  • plus + sign for Shift key
  • caret ^ sign for Ctrl key
  • percent % sign for Alt key

For example, +a represents the Shift+A keys combination, ^+a represents the Ctrl+Shift+A keys combination, and ^+%b represents the Ctrl+Shift+Alt+B keys combination.

Application.OnKey Key:="^+%b", Procedure:="CombinedKeys"

The CombinedKeys procedure will execute when the user press Ctrl+Shift+Alt+B keys. The +, ^, % characters are special characters, to assign a procedure to one of the special characters, enclose the character in braces. For example +{+} represents the Shift++. and ^{+} represents the Ctrl++.

Application.OnKey Key:="+{+}", Procedure:="ProcedureName"

Key Codes for the OnKey Event

To specify characters that aren’t displayed when you press the corresponding key (for example: Enter, Backspace, Break or Tab), use the codes listed in the following table. Each one of them can be used without the need to be combined with others.

Key NameCode
Backspace{BACKSPACE} or {BS}
Break{BREAK}
Caps Lock{CAPSLOCK}
Clear{CLEAR}
Delete or Del button{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 to F15 (functional keys){F1} … {F15}