Assign a Shortcut Key using Excel VBA

.

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

Key (Special Key) Key Code
BACKSPACE {BACKSPACE} or {BS}
ENTER (From Numeric Keypad) {ENTER}
BREAK {BREAK}
ENTER ~ (tilde)
CAPSLOCK {CAPSLOCK}
ESCAPE Key {ESCAPE} or {ESC}
CLEAR {CLEAR}
HELP {HELP}
DELETE DELETE or DEL
HOME {HOME}
DOWN Arrow {DOWN}
INS (Insert Key) {INSERT}
END {END}
LEFT ARROW {LEFT}
NUM LOCK {NUMLOCK}
PAGE DOWN {PGDN}
PAGE UP {PGUP}
RETURN {RETURN}
RIGHT ARROW {RIGHT}
SCROLL LOCK {SCROLLLOCK}
TAB Key {TAB}
UP ARROW {UP}
F1, F2, F3…. F15 {F1}, {F2}, {F3},…..,{F15}

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", ""

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

2 Comments

  1. Keven Guerena

    What’s up, I check your blog regularly. Your humoristic style is witty, keep up the good work!

    Reply
  2. RYAN

    Fantastic information, this was great to know and I had a hard time trying to get a work around to the manual Macro short cut key window.

    Thanks alot

    Reply

Trackbacks/Pingbacks

  1. How to assign a Shortcut key to a Procedure or Subroutine - Let's excel in Excel - […] Assign a Shortcut Key using Excel VBA Assign a Hot Key to a Command Button in Excel […]
  2. Distance Calculator in Excel VBA [REVISED] - Let's excel in Excel - […] Assign a Shortcut Key using Excel VBA […]

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest