As a VBA developer you sometimes need to use a key or a combination of keys to run a procedure without using the command button on a UserForm. This is possible with the VBA onkey method.

Syntax

The syntax for the OnKey method is:

Application.OnKey (key, procedure)

Parameters/Arguments

The syntax has two parameters/arguments which are key and procedure

Key

The Key argument can specify any single key combined with ALT, CTRL, or SHIFT, or any combination of these keys. Each key is represented by one or more characters.

Normal alphanumeric keys are denoted by themselves (e.g. “a”, “A”, “1”).

Example: Assign “ActivateSheet” to key A.

Syntax: Application.Onkey “A”, “ActivateSheet”

It is also possible to use special characters such as + ^ % { ) ~ ) and special keys like BACKSPACE, END etc. To do that, you have to use curly braces {}.

Example: Assign “SelectRange” to a) BACKSPACE key b) % key.

1. Syntax: Application.Onkey “{BACKSPACE}”, “SelectRange”
2. Syntax: Application.Onkey “{%}”, “SelectRange”

Below is the list of keys than can be used and their corresponding codes

Key	Code
BACKSPACE	{BACKSPACE} or {BS}
BREACK	{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	{SCROLLDOWN}
TAB	{TAB}
UP ARROW	{UP}
F1 Through F15	{F1} Through {F15}

You can also use keys combined with Shift and/or Ctrl and/or Alt and/or Command. To do so, use the following table.

To combine keys with	Begin the key code with
Shift	+ (plus sign)
Ctrl	^ (caret)
Alt	% (percent sign)

Example: Assign “CopySheet” to the key sequence Shift+Ctrl+Right arrow.

Syntax: Application.Onkey “+^{RIGHT}”, “CopySheet”

Procedure

It is an optional (can be omitted) string indicating the name of the procedure to be run. When the procedure is omitted, nothing happens when key is pressed, key reverts to its normal function in Microsoft Excel. In other words, if we write the following code:

Application.Onkey “{ENTER}”, the procedure argument has been omitted. In this case when the ENTER key will be pressed; it will simply execute the function that was initially assigned to it by MS Excel. This syntax is used to enable any key that was disabled.

When the procedure argument of the OnKey Syntax is an empty string, it is to disable the said key.

For example, if we want to disable the ENTER key, we shall use the following code:

Application.Onkey “{ENTER}”, “”.

Application

In the following lines we are going to apply the usage of OnKey method with a step by step tutorial for beginners through 2 examples. It is advisable for you follow the steps and do it yourself.

While assuming that you know how to use the VBA/Excel Editor (If not, refer to the following article: How to create a procedure in Excel/VBA from the same author), let’s create 2 procedures and assign key/keys to each of them

Activity Number 1

For a start we shall create a procedure called “Testing” in the Module1 of the VBA Editor and write the following code:

The ENTER key will be assigned to the procedure in Sheet1, meaning that whenever the ENTER key will be pressed the following message will pop up “Hello World, My Onkey Method is Working!!!”

As we saw in the table above the corresponding code for the ENTER key is “~”. So the syntax to assign the ENTER key to the “Testing” procedure is:

Application.OnKey "~", "Testing"

It should be noted that this piece of code can be written directly in the worksheet where we want it to be executed or in a module as a procedure and called from the said worksheet.

If we opt for the first option then, identify the sheet and double click on it and write the following code

Private Sub Worksheet_Activate()
Application.OnKey "~", "Testing"
End Sub

Try it and make sure you get the following message box when you press the ENTER key in Sheet1.

If that is the case then congratulations! You have written your first OnKey method successfully. The next examples will follow the same pattern with less explanation this time.

Activity Number 2

In this second activity, we are going to disable the arrow keys that is {UP}, {DOWN}, {LEFT} and {RIGHT}.

Create a procedure named “DisableArrowkeys” in Module1 and write the following code

Sub DisableArrowKeys()
Application.OnKey "{UP}", ""
Application.OnKey "{DOWN}", ""
Application.OnKey "{LEFT}", ""
Application.OnKey "{RIGHT}", ""
End Sub

Note the use of "" in the place of the procedure argument. This is to indicate that the key has been disabled as it was said earlier. If you wish to enable the key again simply remove it.

See also: Simulating a key press with Application.SendKeys