Dear LEM Readers,
 
In the previous article, you learnt how to assign or modify a shortcut key using excel option. In this article you will learn how to assign shortcut keys to your already written procedure or subroutine using VBA (excel macro). At the end of this article you will be comfortable enough with association shortcut keys for your written macros. But it is a best practice to assign a shortcut key using excel option because when there is a simple option available in excel to do this then why to write code and execute it. This article I am posting for you to know that “Yes it is possible using excel VBA code as well.”
 
There are two ways of assigning a shortcut key to a macro in excel vba. One is using Application.OnKey and other one is using Application.MacroOptions. Here in this article we are going to learn both the techniques.
 

Method 1 : Assign a Shortcut Key using OnKey in Excel VBA

OnKey is an Application method which makes a particular procedure run in excel vba when a specific key or combination is pressed.

Syntax:
Application.OnKey, <Key as String> , < Procedure>

Where:
Key as String: is the Key combination you want to assign to your procedure.
Procedure: is the name of the procedure which you want to be run.

Example:

To assign a Shortcut Key CTRL + b for a Subroutine or procedure named MyProgram then execute the below code and you are done…

Application.OnKey "^b", "MyProgram"

 
As soon as you execute the above statement, Shortcut Key CTRL + b will be assigned to the macro named “MyProgram” and pressing the key combination CTRL + b will trigger the procedure MyProgram
 
In the above example you can see I have used Caret Sign (^) before the letter b. Caret Sign is used for CTRL Key. Similarly percentage sign % is used for ALT Key and Plus Sign + is used for Shift Key.
 

Method 2 : Assign a Shortcut Key Application.MacroOptions

Syntax:

Application.MacroOptions Macro:=”<Macro Name>”, Description:=”<Description of Macro>”, HasShortcutKey:=True, ShortcutKey:=”<Your Shortcut Key>”

Example:

To assign a Shortcut Key CTRL + b for a Subroutine or procedure named MyProgram then execute the below code and you are done…

Application.MacroOptions macro:="MyProgram", Description:="Description of the Macro", _
hasshortcutkey:=True, ShortcutKey:="^b"

 
As soon as you execute the above statement, Shortcut Key CTRL + b will be assigned to the macro named “MyProgram” and pressing the key combination CTRL + b will trigger the procedure MyProgram
 
In the above example you can input the description of the Shortcut Key as well.

Advantage of Using VBA Code for assigning a Shortcut Key

As you have seen in the previous article to assign Shortcut key in excel options, there you can assign only a key which can be typed in the Option box. It means all the alphabets and numbers you can assign as a Shortcut key but what about special keys on the keyboard which can not be typed like Backspace, Enter Key, Delete Key etc.?? Using the VBA code you can assign these special keys as a shortcut key for your procedure.
 
Below table gives you the Key Name for all the special keys on the keyboard which needs to be passed in your VBA code

How to de-assign or release a Shortcut Key which already assigned using vba code

You can pass empty or blank procedure name in the above code to release or de-assign the shortcut key. So if we want to release the Shortcut key CTRL + b which is assigned in the above code, you can run the below statement:

' Procedure Name is passed as blank to release CTRL + b

Application.OnKey "^b", ""