Software

How to add Office macros to the QAT toolbar for quick access

It's easy to add a macro or two to the Office QAT. And if that toolbar gets too crowded, just create a custom tab and group your macros by task.

    hero
    Image: iStockphoto.com/hikmetyildiz

    Macros are procedures written in Visual Basic for Applications (VBA) that automate your work. If you use Office, you're probably familiar with them and you might even have a few. Some are easy to manage. You can use the Developer tab or assign a shortcut to execute them. But if you have a macro you use often—or you have several you access regularly—you might be looking for an easier way to manage things. In this article, I'll show you how to add a macro to the Quick Access Toolbar (QAT). I'll also show you how to create a custom tab so you can corral several macros.

    I'm using Word 2016 on a Windows 10 64-bit system, but you can use any of the Office apps. The instructions for earlier Ribbon versions aren't significantly different. You can't easily customize the Ribbon in Office 2007, so the custom tab instructions don't apply to that version. However, you can add a macro to the Office 2007 QAT. This article isn't relevant to menu versions. To work through this process, all you need is a simple macro. If you don't have one, you can download the sample .docm file.

    The QAT

    Office Ribbon-version apps provide the QAT for speedy access to popular commands. You can also add a macro to it, and the process is simple. If you don't have a macro, create one - it doesn't have to actually do anything. You just need a sub procedure your app can see. To add the macro to the QAT, do the following:

    1. Click the QAT dropdown and choose More Commands (Figure A).
    2. From the Choose Commands From dropdown, choose Macros (Figure B).
    3. Your app will display sub procedures (macros) in the resulting list. Select the macro you want to add to the QAT and click Add (Figure C).
    4. Click OK to add the macro to the QAT (Figure D).

    Figure A

    Figure A
    From the QAT dropdown, choose More Commands.

    Figure B

    Figure B
    Macros are one of the many choices.

    Figure C

    Figure C
    Choose and add the macro.

    Figure D

    Figure D
    Now your macro is a quick click away on the QAT.

    If you add a single macro, the default icon is adequate. However, all macros share the same default icon, so you might want to change it. You can do so when adding the macro to the QAT or after the fact, as follows:

    1. Repeat steps 1 and 2 above to access the QAT interface options.
    2. Select the macro in the Customize Quick Access Toolbar list.
    3. Click Modify.
    4. In the resulting dialog, choose a new icon (Figure E). You can also change the name.
    5. Click OK twice to see the new icon (Figure F) in the QAT.

    Figure E

    Figure E
    Choose a new icon.

    Figure F

    Figure F
    Click the macro button to execute the macro from the QAT.

    A custom tab

    Adding a few macros to the QAT is probably adequate for most of us. But if you have several macros, consider creating a custom tab and grouping them by task. You just need to create a new tab, add a new group to the tab, and then add the macro to the new group, as follows:

    1. Click the File tab and choose Options.
    2. In the left pane, choose Customize Ribbon.
    3. Below the Customize The Ribbon list, click New Tab to add a custom tab. If you don't like its placement, use the arrows to the right to move it.
    4. Right-click the new tab, choose Rename, enter Macros, and click OK.
    5. Click New Group (Figure G) to add a new group.
    6. Change its name to Fun Message Macros.
    7. With the new group selected in the Customize Ribbon list, choose Macros from the Choose Commands From dropdown (to the left).
    8. Select the macro and click Add to put the macro in the new group on the new Macros tab (Figure H).
    9. Right-click the macro and change its name and icon.

    Figure G

    Figure G
    Add a custom tab.

    Figure H

    Figure H
    Add the macro to the custom group.

    To use the macro, just choose the new Macros tab (Figure I). You can add as many macros to the tab as you like. If you have lots of macros, you might want to group them by process or task by adding appropriately named groups to the custom Macros tab.

    Figure I

    Figure I
    Adding macros to a custom tab provides quick access.

    Two alternatives

    You don't need to add a macro to the QAT or add a custom tab. You can also execute macros via the Developer tab or keyboard shortcuts. Execute a macro from the Developer tab as follows:

    1. Click the Developer tab.
    2. In the Code group, click Macros.
    3. In the resulting window, select the macro (Figure J) and then click Run.

    Figure J

    Figure J
    Select a macro and click Run to execute it.

    The Developer tab isn't always available, though, and you might not want to enable it. That's a good reason to add a Macros custom tab. The other alternative is to assign a keyboard shortcut. This option is great for a few macros, but you must memorize the shortcuts—so you'll probably want to keep them to a minimum. To assign a shortcut to an existing macro, do the following:

    1. Click the File tab and choose Options.
    2. In the left pane, choose Customize Ribbon.
    3. Below the Choose Commands From list on the left, click Customize.
    4. From the Categories list, choose Macros.
    5. Select the macro in the Macros list to the right.
    6. Click inside the Press New Shortcut Key control and press the keys you want to assign to this macro. Office will display a current keystroke assignment if one exists (Figure K). In that case, you can enter a new combination or overwrite the existing one.
    7. Click Assign.
    8. Click Close and then OK.

    Figure K

    Figure K
    Assign a shortcut to a macro.

    If you overwrite an existing shortcut, you can reset it by removing the new one. Repeat steps 1 through 5, select the macro, and click Remove.

    QAT, custom tab, Developer tab, shortcut

    In this article, we added the same macro to the QAT and a custom tab. You can do either or both if you have enough macros to go around. Neither option is superior to the other, but grouping related macros on a custom tab will make them easier to find. Both options are superior to the Developer tab, which is great for testing but not regular use. Shortcuts are fine when you only have to memorize a few.

    Send me your question about Office

    I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

    Also read...

      About

      Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

      Editor's Picks

      Free Newsletters, In your Inbox