Skip to Main Content

go to Indiana University Indiana University


Excel 2010: Assigning Keyboard Shortcuts to Macros and Adding Macro Buttons

Do you find yourself forgetting keyboard shortcuts for your Excel macros?  When first recording a macro, you will have an opportunity in the Record Macro dialog box to specify a shortcut key for the macro. However, you may eventually forget the shortcut, or if you didn’t assign a shortcut key when you first recorded the macro, you may now like to assign one – or perhaps you have even decided to change the shortcut key to something different.

To view your macro shortcut keys, on the View tab of the Ribbon, click the Macros button, select your macro, and choose Options….  You will see the Macro Options dialog box:

dialog box for Macro Options

This dialog box shows the shortcut key.

Here you can view the macro shortcut and description, assign a shortcut, or modify the existing shortcut keys.  When revising shortcuts, it doesn’t matter whether you saved the macro in this workbook or in the Personal Macro workbook. Just make sure that you do not use an existing Excel keyboard shortcut (such as CTRL B, which bolds a cell). In addition, use a single letter with the CTRL or Shift keys.

Note: The CTRL + SHIFT + single letter keyboard combination may work best for a macro shortcut due to the many Excel shortcuts which already use the CTRL + single letter combinations.

Adding a Macro Button to the Quick Access Toolbar

Rather than creating a keyboard shortcut, you can add a macro button to the Quick Access Toolbar. (In Microsoft Office 2010, it is possible to add a new group to the Ribbon so you may prefer to add a Macro group that will include all of your macros).

To add a macro to the Quick Access Toolbar:

  1. Open the Excel Options dialog box and click the Quick Access Toolbar link in the left pane. (If you want to add the macro to the Ribbon, choose Customize Ribbon. You will have the option to add a new group to the Ribbon to which you can add your macros).
  2. In the “Choose Commands From” drop-down menu, select Macros. Excel macros, including your custom macros, will display.
  3. Select the macros that you want to add to the Quick Access Toolbar, and click Add >>.  Note that there is a <Separator> at the top of the list, which you can add to separate the macros from other commands on the Quick Access Toolbar).
  4. Then click the Modify… button at the bottom of the right pane if you want to rename the macro or if you want to select a different icon.

Once you click OK, the macros will appear on the Quick Access Toolbar and will always be available while you are working on that same computer.

Adding a Macro Button to the Worksheet

You can also add a macro button to the worksheet by following these steps:

  1. On the Developer tab of the Ribbon, in the Controls group, click the Insert button. Choose  Button (Form Control).
  2. Press and drag somewhere in the worksheet to create a button.
  3. The Assign Macro dialog box appears, which displays all of your macros. Select the desired macro and click OK.
  4. The button appears on the worksheet. You can change the text of the button while it is selected or at a later time, you can right-click the button and choose Edit Text. You can also format the button by right-clicking the button and choosing Format Control….

 


8 Responses to "Excel 2010: Assigning Keyboard Shortcuts to Macros and Adding Macro Buttons" to “Excel 2010: Assigning Keyboard Shortcuts to Macros and Adding Macro Buttons”

  1. Hannah Says:

    Dear sir,
    Please advise how to list out all macro name with shortcut key instead check each macro one by one.

  2. Susan Says:

    Hannah,
    Great question! I don’t know of any way to list all macros with shortcuts; to view shortcut for a macro, select a specific macro from the Macro dialog box and then click Options. It would be a good idea to keep a list of your macros with shortcuts somewhere for easy access.

  3. Mark Says:

    Hi
    Excel 2010
    Macros / view macros / dialogue box appears which does not contain “options”
    contains Run, Step into, Edit, Create, delete, Organizer.

    Please where are options after the macro has be created?

    Mark

  4. Mark Says:

    Hi Again
    My mistake. I am in Excel all the time but this time I was in Word. Of course options is in Excel. I will now start to look for my word answer.
    No wonder i could not find an answer.

    mark

  5. Tom Mason Says:

    Glad you got it all figured out, Mark!

  6. JimM Says:

    How do I change an assigned shortcut from Ctrl+Shift+letter to just Ctrl+letter?

    Thank you for your time and trouble.

  7. JimM Says:

    By the way, “http://shortcutworld.com/en/win/Excel_2010.html” gives some info on the pre-assigned shortcut key combinations.

  8. Susan Says:

    JimM,
    Open the Macro dialog box (View Macros), select the macro, and click the Options button on the right. Delete the letter and type it again without the Shift key. This will change the shortcut to the assigned Ctrl+letter – AND this will also override any built-in Excel shortcuts that already are using that key combination.

Leave a Reply

 

Get Training Tips via RSS Subscribe to Entries feed or Read Comments via RSS Subscribe to Comments feed.