How To Run A Macro Using A Button On The Excel Quick Access Toolbar

This article will tell you how to create a macro in excel, and how to add a button in excel quick access toolbar to call the macro. In this way, you can run your excel macro easily and quickly.

1. How To Create A Macro In Excel.

  1. Open an excel workbook, and click the first icon record macro button icon ( an icon like a smaller table ) on the bottom left corner.
  2. Then it will open the Record Macro pop-up dialog.
  3. Give the macro a name such as unhideAllWorksheets in the macro name input text box.
  4. Select the item Personal Macro Workbook from the drop-down list Store macro in.
  5. Click the OK button to create the excel macro.
  6. Now you will find the Record Macro button icon has been changed to a small gray square in the bottom left corner of the excel window, click it to stop recording the macro.
  7. Open the VBA editor window, you can read the article How To Enable Developer Tab And Write VBA Source Code In Excel to learn how to do it.
  8. In the VBA window left side project list panel, click the VBAProject(PERSONAL.XLSB) —> Modules —> Module1 to open the macro source code editor window.
  9. Now, you can add macro source code in the right-side source code editor window.
  10. After you edit the macro source code, you should save the excel file to .xlsm format.
  11. Because this file format can reserve the macro source code from being lost.
  12. You can choose this file extension when you click the Save button to save the file.
  13. It will prompt a dialog and tell you that you should select a macro-enabled extension to save the macro.
  14. Then you can select the Excel Macro-Enabled Workbook (*.xlsm) from the Save as type drop-down list.
  15. Input the file name in the File name text box, and click the Save button to save it.
  16. Now when you open this .xlsm file for the next time, you can run the macro saved in this file again.

2. How To Add A Button In Excel Quick Access Toolbar To Call The Macro.

  1. Click the excel File —> Options item to open the Excel Options dialog window.
  2. Then click the Quick Access Toolbar item on the Excel Options window left side.
  3. Select the Macros item from the drop-down list Choose commands from.
  4. Select the macro that you just created from the macro list below the above drop-down list.
  5. Click the Add button to move the macro from the left list to the right list.
  6. You can also click the Modify… button below the right list to select a customized icon for the macro command.
  7. Click the OK button to save the above changes.
  8. Now, you can see the icon in the excel quick access toolbar, when you click the icon button, it will invoke the macro command.
  9. The quick access toolbar is located on the excel top side above the ribbon area by default.
  10. There is a down arrow on the right side of the quick access toolbar.
  11. When you click the down arrow, you can click the Show Below the Ribbon menu item to move the quick access toolbar below the ribbon area.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.