Run A Macro From A Shortcut Key
May 07, 2021 - by Bill Jelen
Challenge: You recorded a macro but forgot to assign it to a shortcut key. Now you need to change the shortcut key used for the macro. Excel documents the shortcut key used when recording a macro in the comments at the top of the macro. However, changing the comment in the macro does not have any effect on the actual shortcut key used.
Solution: To change the shortcut key, press the F8 key to see a list of macros. Click the macro in question and click the Options button in the Macro Options dialog. You can edit the shortcut key here (Figure 104).
Additional Details: You can temporarily assign a macro to a shortcut key by using a macro. Perhaps you want to turn on a shortcut key during one section of a process and turn it off later in the process. The following line of code temporarily assigns the MoveDown procedure to Ctrl+m:
To cancel this assignment and return Ctrl+m to its normal function, use:
To permanently change the shortcut key via code, use:
Additional Details: Excel stores the shortcut key in the code module, but it is not visible in the Visual Basic editor. You have to export the module from VBA. Follow these steps:
- Right-click the module in the Project Explorer and choose Export File.
- Save the file in a place where you can find it later. Excel proposes a name such as Module1.bas. You can use this name.
- Open the .bas file in Notepad.
- You see an attribute near each module. The shortcut key is listed, followed by /n14. So, in Figure 105, the g\n14 attribute means the macro is assigned to Ctrl+g. The G\n14 attribute means the macro is assigned to Ctrl+Shift+G.
Summary: There are several ways to assign the shortcut key associated with a macro.
Source: Need help creating shortcut keys to add-in macros on the MrExcel Message Board.
Title Photo: Florian Krumm on Unsplash
This article is an excerpt from Excel Gurus Gone Wild.
