Excel macros are very useful tools that save you time when you need to perform a sequence of repetitive operations. By assigning a macro to a custom button, you can speed up your work even further as it only takes a click of the mouse to execute it.
Steps
Method 1 of 4: Use Excel 2003
Step 1. Click the Tools menu and select the option Customize.
Step 2. Click the "Toolbars" tab
Step 3. Click on the "New" button
Step 4. Type the name you want to give your new toolbar
Step 5. Click on the "OK" button
Step 6. Click the "Commands" tab
Step 7. Select the "Macros" item from the left pane of the "Commands" tab
Step 8. Drag the Custom Button icon from the right pane of the "Commands" tab to the new toolbar you just created
The custom button icon has a yellow smiley face.
Step 9. Select the new button with the right mouse button
Step 10. Rename the newly created button to whatever name you like (or you can keep the default)
If you have chosen to rename it, enter the new name in the "Name:" text field.
Step 11. Click the "Edit Button Image" option
.. and choose whether or not to change the default button image.
The Excel image editor uses controls very similar to the Windows Paint program.
Step 12. Click on the "Assign Macro" item
Step 13. Select one of the macros you created earlier using the appeared list
Step 14. Click on the "OK" button
Step 15. Now click on the "Close" button in the "Customize" dialog box
Method 2 of 4: Use Excel 2007
Step 1. Click the small down arrow icon on the right of the Excel 2007 Quick Access Toolbar
Step 2. Click the More Commands option
..
Step 3. Select the Macro item from the list in the drop-down menu Choose commands from.
Step 4. Now select the macro you want to use from the list in the left pane of the window, then click on the "Add" button
Step 5. Select the macro you just added to the right pane of the window and click the "Edit" button
Step 6. Click on the image you want to assign to the button linked to the selected macro, type the name that will be displayed on the button in the Display Name text field and finally click on the button OK.
Method 3 of 4: Use Excel 2010
Step 1. Make sure the "Developer" tab of the Excel ribbon is visible
It is located at the top of the program window. If the card in question is not visible, follow these instructions:
- Click on the "File" menu, choose "Options" and select "Customize Ribbon".
- Find the "Development" checkbox listed in the "Main Tabs" box and click it with the mouse. As a last step, press the "OK" button.
Step 2. Add a "New Group" to the "Development" tab to create a custom group of options in which to insert the button of the macro you are going to create
Step 3. Remaining in the "Customize Ribbon" tab, click the "Choose commands from" drop-down menu and select the "Macros" option
At this point all the macros you recorded previously will be displayed in the left pane of the window.
Step 4. Select the macro you want to assign to the new button you are going to create (make sure the new group you added is highlighted
You will know that the macro has been assigned correctly when the corresponding name will appear in the right pane of the window below that of the selected group).
Step 5. At this point you can customize the button in question
Select it with the right mouse button and choose "Rename".
Step 6. After customization is complete, click on the "OK" button
Method 4 of 4: Use Excel 2013
Step 1. Make sure the "Developer" tab of the Excel ribbon is visible
It is located at the top of the program window. If the card in question is not visible, follow these instructions:
- Access the "Excel" menu, choose the "Preferences" option, then click on the "Ribbon and Quick Access Bar" item (it is located in the "Sharing and Privacy" section).
- Select the "Development" checkbox listed in the "Main Tabs" pane of the "Customize Ribbon" section, then click the "OK" button.
Step 2. Click on the "Development" tab, then click the "Button" item
The latter icon is listed in the "Form Controls" menu of the "Insert" option of the "Developer" tab and is characterized by a small rectangular button.
Step 3. Place the newly created button where you want it
Place it in the place of the interface you prefer, then drag the anchor points to resize it according to your needs. You can zoom in or out as you like. After placing it, you can still choose to move it to another point in the window at any time.
Step 4. Assign a macro to the button when prompted
Excel should automatically ask you to assign a macro to the new button as soon as you place it where you want it. After selecting the macro in question, click on the "OK" button.
If you don't use macros frequently or if you want to find out how to record one, read the articles indicated. Before you can create a button to run a macro, it must have already been created
Step 5. Customize the new button
Select it with the right mouse button and choose the "Format Control" option. Select the "Properties" item, choose the "Do not move or resize with cells" option and click the "OK" button. This way the button cannot be moved or resized. Without selecting the indicated option, the position and size of the button will automatically change if you add, delete or move cells.
Step 6. Rename the button
Change the text displayed inside the button to the name of your choice.
Advice
- If you are using a version of Excel that is earlier than Excel 2003, still try to use the sequence of steps described in the method of the corresponding article.
- Alternatively, using Excel 2003 or an earlier version you can add a button to run the macro directly on the existing toolbar.
- If you wish, you can also assign a hotkey combination to run the macro. This way you can save time and effort.
Warnings
- The user interface of earlier versions of Excel 2003 may differ from that of this latest version, so the steps described for creating and using a macro in Excel 2003 may not be accurate when used in earlier versions of the program.
- If you need to use a button icon other than the one that Excel 2007 offers, you will need to download and install additional software that can modify Excel user interface elements.
