Macros in Excel can save a lot of time on repetitive work. By adding macros to custom buttons, you can save even more time by making macros run with just one click.
Step
Method 1 of 4: Excel 2003
Step 1. Click Tools → Customize
Step 2. Click the Toolbars tab
Step 3. Click the New button
Step 4. Type a name for your new toolbar
Step 5. Click OK
Step 6. Click the Commands tab
Step 7. Select Macros in the list on the left side
Step 8. Click and drag the Custom Button icon from the list on the right side of your new toolbar
This new button is represented by a smiling face icon.
Step 9. Right-click on the newly added button
Step 10. Rename the button to your liking or leave the default name at Name:
fields.
Step 11. Click Edit Button Image
.. and change the image for your button or leave it the same. The Button Editor has settings similar to the Windows Paint program.
Step 12. Click "Assign Macro"
Step 13. From the list, select the macro you created
Step 14. Click OK
Step 15. Click Close in the Customize dialog box
Method 2 of 4: Excel 2007
Step 1. Click the small down-pointing arrow on the Quick Access Toolbar
Step 2. Click More Commands
Step 3. Select Macros from the list box Choose commands from.
Step 4. Select your macro from the column on the left side and click the Add button
Step 5. Select the macro you just added from the column on the right side and click the Modify button
Step 6. Click the button image you want as your macro representation, type the display name you want in the Display name text box, then click the ' button OK'.
Method 3 of 4: Excel 2010
Step 1. Make sure the Developer tab is visible
The Developer tab is the tab on the Ribbon at the top of Excel. If the tab is not visible, follow these instructions to display it:
- Click File → Options → Customize Ribbons
- Find the Developer checkbox under Main Tabs and click it. Press “OK” when finished.
Step 2. Add “New Group” under the Developer tab to create a custom group for the commands/buttons to be created
Step 3. Still in the Customize Ribbon, click the drop-down menu to select a command
Select Macros. After that, all the macros that have been recorded will appear in the box on the left side.
Step 4. Select the desired macro for the button creation (make sure the newly added group is highlighted, you will know if the macro has been added when it appears in the box on the right side under your new group)
Step 5. Now you can customize your button
Right-click and select Rename.
Step 6. When everything is set, click "OK"
Method 4 of 4: Excel 2013
Step 1. Make sure the Developer tab is visible
The Developer tab is the tab on the Ribbon at the top of Excel. If the tab is not visible, follow these instructions to display it:
- Click Excel → Preferences → Ribbon (under Sharing and Privacy)
- Under Customize, check the box next to the Developer tab, then hit “OK”.
Step 2. Click the Developer tab and click the Button
The Button icon is below the Form Controls group on the Developer tab and looks like a rectangular button.
Step 3. Place your buttons
Hover over the location where you want the button to be, then drag to select the button size. You can make the button as small or as large as you want, according to your needs. If necessary, you can swipe the button after it's placed.
Step 4. Add a macro when prompted
Excel will automatically ask you to add a macro to your button after you finish placing it. Once you have selected your macro, click "OK."
If you don't know what macros are or how to record them, read on. You should have created the macro before creating the button
Step 5. Format the button
Right-click on the newly created button, then select “Format Control”. Select Properties → Don't move or size with cells → OK. This will help you to maintain the size and placement of your buttons. If you deselect this property, the size and placement of your buttons will change if you add, delete, or move cells.
Step 6. Rename the button
Change the text on the button to give it any name you like.
Tips
- Try using the 2003 method for Excel with a version earlier than 2003.
- Alternatively, you can add your macro buttons to the existing toolbar in 2003 and earlier versions.
- If you like, you can add shortcut keys to the dialog box. This can prevent strain injuries to the wrist and save time.
Warning
- The user interface in versions of Office that are earlier than Office 2003 may be different. So, the method for Office 2003 may not be exactly the same for those versions.
- If you want a different button image than the 2007 version, you will need to download special additional software to change the user interface for Microsoft Office.
