While working in Excel, we often need to use macro in our worksheets and it is easier to use an assigned macro shortcut key than using the macro button. Using the macro shortcut key can save a lot of time while dealing with large datasets. In this article, we are going to learn about 2 simple methods to assign and edit macro short key in Excel.
Excel Macro Shortcut Key (2 Suitable Ways)
In the following dataset, we have the Monthly sales data of a tech store. Our goal is to assign a macro shortcut key and later on, we will learn how we can edit an assigned macro shortcut key in Excel.
Not to mention that we have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.
1. Using Record Macro Option to Assign Excel Macro Shortcut Key
Using the Record Macro option is one of the easiest methods to assign a marco shortcut key in Excel. Let’s follow the steps mentioned below to do this.
Steps:
- Firstly, go to the Developer tab from Ribbon.
- Following that, select the Record Macro option from the Code group.
As a result, the Record Macro dialogue box will open as shown in the following image.
Note: You can also use the keyboard shortcut ALT + L + R to open the Record Macro dialogue box directly from your worksheet.
- Now, from the Record Macro dialogue box, rename the Macro name. Here, we have used the name Sample_macro.
- After that, in the Shortcut key box, type in the shortcut key combination you want to use to enable this macro. In this case, we used CTRL+ U (lowercase U).
Note: Assigning the macro shortcut key is case-sensitive. If you enter an uppercase letter, then the shortcut key combination will be CTRL + SHIFT + your assigned key. It is demonstrated in the following picture.
- Subsequently, click on the drop-down icon under the Store macro in option.
As a result, you will see 3 options in the drop-down menu.
- Personal Macro Workbook → Choosing this option will permanently save this macro shortcut key for your workbook.
- New Workbook → Selecting this will save the macro shortcut key in a new workbook.
- This Workbook → By choosing this, the macro shortcut key will only be active in the current workbook.
- Now, choose the This Workbook option from the drop-down. You can choose other options according to your requirements.
- Then, click on OK.
- Finally, click on the Stop Recording option from the Code group under the Developer tab and your macro shortcut key will be added.
Read More: Types of VBA Macros in Excel
2. Editing Excel Macro Shortcut Key of Assigned Macro
In this portion of the article, will discuss how we can edit an assigned macro shortcut key in Excel. Let’s use to steps demonstrated below.
Steps:
- Firstly, go to the Developer tab from Ribbon.
- Following that, select the Macros option from the Code group.
Consequently, the Macro dialogue box will be visible on your worksheet as shown in the following image.
Note: You can also use the keyboard shortcut ALT + L + PM to open the Macro dialogue box directly from your worksheet.
- After that, in the Macro dialogue box, choose the macro of which you want to edit the shortcut key.
- Then, click on the Options as marked in the image given below.
As a result, the Macro Options dialogue box will open.
- Next, from the Macro Options dialogue box, edit the Shortcut key according to your preference. In this case, we changed the shortcut key to CTRL + R (lowercase R).
- Then, click on OK.
- Afterward, you will be redirected to the Macro dialogue box and click on the Cancel option.
- Finally, click on Stop Recording.
Consequently, your macro will be assigned with the new macro shortcut key.
Read More: How to Edit Macros in Excel
Things to Remember
- While assigning the macro shortcut key, if you have CAPS LOCK enabled, the shortcut key combination will be like CTRL + SHIFT + your specified key.
- If you have 2 or more workbooks that have macros that contain the same macro shortcut key, then the macro that will run is the macro that is alphabetically first in the list of available macros.
Download Practice Workbook
Conclusion
That’s all about today’s session. I strongly believe that this article was able to guide you to assign and change macro shortcut key in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality.
Related Articles
- How to Save VBA Code in Excel
- Using Macro Recorder in Excel
- How to Record a Macro in Excel
- 25 VBA Macro Example for Enhanced Productivity
- How to Remove Macros from Excel
- How to Save Macros in Excel Permanently
- How to Use Excel VBA to Run Macro When Cell Value Changes
- Excel VBA to Pause and Resume Macro
- Excel Macro Enabled Workbook