How to assign a keyboard shortcut to a macro in Excel

A macro is a special program code implemented in the Visual Basic for Application programming language integrated into the spreadsheet editor. What usually happens is that a spreadsheet editor user creates a custom macro in the built-in VBA programming language and then applies it to a spreadsheet document worksheet using several dialog boxes. This procedure takes too long, especially if the spreadsheet file contains large amounts of information. Each macro can be assigned its own combination of hot keys, which allows you to instantly apply it on the worksheet of a spreadsheet document. In the article, we will take a closer look at the process of assigning keyboard shortcuts to a macro in a spreadsheet editor.

What is a macro in spreadsheet editor

First, let’s define what a macro is. A macro is a special tool that is designed to create commands in a spreadsheet editor, which can significantly speed up the process of performing various tasks.

Enabling a macro in the spreadsheet editor

Before assigning hotkeys to a macro, you need to make sure that the macros themselves are activated. It may happen that for one reason or another they are turned off and then it will be impossible to assign hotkeys. Detailed instructions for enabling macros look like this:

1. Using the left mouse button, we move to the “File” subsection, located in the upper left corner of the spreadsheet editor interface.
2. We move to the subsection, which has the name “Parameters”.
3. In the parameters window that appears, we move to a fad called “Security Control Center”.
4. Click on Trust Center Settings.

5. A window appeared on the display with the name “Security Control Center”. We move to the “Macro Options” subsection.
6. Check the box next to “Enable all macros”.

7. Ready! We have enabled macros in the spreadsheet editor. Now we can start assigning special hotkeys to the macro.

Procedure for assigning hotkeys to a macro

In the spreadsheet editor, each macro can be assigned hotkey combinations. The Visual Basic for Application programming language code stores the key combination that runs the macro. For example, we have a ready-made user macro in which we need to add a hotkey combination. Let’s see in detail how to implement this difficult procedure.

Detailed instructions for assigning hotkeys to a macro look like this:

1. Initially, we need to get into the window called “Macro”. There are several options for entering this window. The first option: move to the “View” subsection, open the list called “Macros” and select the element with the same name “Macros”. The second option: move to the “Developer” subsection, and then select the “Macros” element. In addition, the “Macro” window can be accessed using the special key combination “Alt + F8”.

2. In the small window that appears, use the left mouse button to select the macro to which we want to assign hot keys. We click LMB on the element “Settings …”.
3. In the window that appears, in the “Keyboard Shortcut:” item, we assign hot keys to the selected macro. For example, let’s make a combination “Ctrl + m”.

It is worth noting that such a macro will only run when using the English keyboard layout.

4. In the “Description:” field, you can enter any auxiliary information for yourself. This field is optional.

5. After carrying out all the manipulations, left-click on the “OK” button located at the bottom of the “Macro Settings” window.
6. Ready! We have assigned a custom keyboard shortcut to the macro.

It is worth noting that you can always change the combination by simply returning to the same window and entering another combination.

There are situations when the user needs to assign hotkeys to a macro only temporarily. For example, the user needs to activate a combination at a certain moment, and then deactivate it and continue working with a spreadsheet document. To implement this procedure, you need to add some lines to the macro program code.

• This line allows you to temporarily assign the “Ctrl + m” button combination to the MoveDown procedure: Application.on key Key:= “^m“, –:=”MoveDown«
• This line allows you to return to the standard definition of the key combination “Ctrl + m”: OnKey Key: =»^m»
• This code snippet allows you to permanently change the shortcut button in the table editor: Application.MacroOptions Macro:=”MoveDown“,
  ShortcutKey:=»m»

It is important to understand that the table editor saves hotkeys in the module code, but they are not displayed in the editor VBA. If the user needs to implement the visualization of hot keys, then he needs to export the module from the VBA editor. Detailed instructions look like this:

1. Moving to the editor
2. Press RMB modulo. In the context menu that appears, select the “Export File” item.
3. Let’s save the file.
4. Using Notepad, we open a file in *.bas format.
5. In any module there is a line with special attributes that begin with the Attribute variable. You can determine the assigned combination of buttons by the code fragment n14. For example, in the image below you can see that the assignment of the key combination “Ctrl + m” is implemented.

Conclusion and conclusions about assigning hotkeys to a macro

We figured out how to implement the procedure for assigning hotkeys to a macro. Initially, you need to make sure that macros are activated in the spreadsheet editor, and then use the options window to assign the desired combination. With a few lines of code in VBA, you can temporarily enable an assigned combination.