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.
- Open an excel workbook, and click the first icon record macro button icon ( an icon like a smaller table ) on the bottom left corner.
- Then it will open the Record Macro pop-up dialog.
- Give the macro a name such as unhideAllWorksheets in the macro name input text box.
- Select the item Personal Macro Workbook from the drop-down list Store macro in.
- Click the OK button to create the excel macro.
- 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.
- 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.
- In the VBA window left side project list panel, click the VBAProject(PERSONAL.XLSB) —> Modules —> Module1 to open the macro source code editor window.
- Now, you can add macro source code in the right-side source code editor window.
- After you edit the macro source code, you should save the excel file to .xlsm format.
- Because this file format can reserve the macro source code from being lost.
- You can choose this file extension when you click the Save button to save the file.
- It will prompt a dialog and tell you that you should select a macro-enabled extension to save the macro.
- Then you can select the Excel Macro-Enabled Workbook (*.xlsm) from the Save as type drop-down list.
- Input the file name in the File name text box, and click the Save button to save it.
- 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.
- Click the excel File —> Options item to open the Excel Options dialog window.
- Then click the Quick Access Toolbar item on the Excel Options window left side.
- Select the Macros item from the drop-down list Choose commands from.
- Select the macro that you just created from the macro list below the above drop-down list.
- Click the Add button to move the macro from the left list to the right list.
- You can also click the Modify… button below the right list to select a customized icon for the macro command.
- Click the OK button to save the above changes.
- Now, you can see the icon in the excel quick access toolbar, when you click the icon button, it will invoke the macro command.
- The quick access toolbar is located on the excel top side above the ribbon area by default.
- There is a down arrow on the right side of the quick access toolbar.
- 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.