Dear LEM (Learn Exel Macro) Readers,

It’s been long time I wrote any article or answered any of your queries. Apologies for the delay in response. I am back with many more amazing articles now. All your un-answered questions will be answered in few days with a set of few interesting articles which will make you awesome in excel VBA 🙂

So here is the first article…which will tell you different ways of assigning Shortcut Keys to your code

It always seems magical when you press a key or a combination of keys and the complete code ran in background and you got the end result without clicking on any button on UI (User Interface / VBA Form). In this article you will learn the same magic, How to assign a Shortcut Key to a Macro or Subroutine written by you or recorded by you. Mainly there are three ways of doing so and they are very easy to do. First two methods are done from Macro options available in Excel and the last one is done in excel vba code. This 3rd way will be explained in the next article with the proper code and a downloadable example sheet practice on it.

Method 1:

Assigning a Short Key while recording a New Macro : Follow the below mentioned steps to assign a Short Key to a Macro while recording it.
 
Step 1.Launch Record Macro Dialog Box as show in the picture below. Click to know how to record a macro
 

 
Step 2. Enter your Shortcut Key which ever you like it in the Shortcut Key box as highlighted in the above image.
 
Step 3. Click OK and Stop the recording.
 
Step 4. Now Press ALT + F11 to see the VBA Code window
 
Step 5. Now Go to the Module to see your Subroutine which is assigned a Shortcut Key as shown in the below picture.
 

 
Step 6. In the above placeholder you can write your complete code what you want it to do on pressing Shortcut Key assigned.
For example in the Above example, Shortcut Key assigned would be CTRL + p
 
Note: Shortcut key is case sensitive.
 
Lets write one line code inside that module as shown below:
 

Sub Macro1()

' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+p
'
MsgBox "You have just pressed the Shortcut Key CTRL + P "
End Sub

Now from the workbook as soon as you press the Shortcut Key CTRL + P message box statement will be executed and one message box will be displayed as shown below:

Method 2:

Assigning a Short Key to an already written Macro or Subroutine : Follow the below mentioned steps to assign a Short Key to a Macro which is already written in any module:
 
Step 1. Go to your Worksheet and Press ALT + F8 or go to Developer Tab and click on Macro as shown in below image:
 

 
Step 2. Select Your particular macro or Subroutine from the dropdown and click on Option as shown below:
 

 
Step 3. Now Enter your Shortcut Key in the box and click Ok. That particular short cut key is now assigned to the selected subroutine.

Limitations of assigning a Shortcut Key using Excel option

Using excel option as explained above, you can not assign a special Key as Shortcut key for your macro. Special Key means, the keys which can not be typed in the shortcut key box like Backspace, Enter Key, All Function Keys like F1, F2 etc.
 
This limitation can be overcome by assigning a shortcut key using VBA code.
 

If you are assigning a shortcut key using excel vba code, then you will be able to assign these special keys as a shortcut key for your macro. Read the Next Article to assign a Shortcut Key using excel vba code.