How to Create Macro Buttons in Excel

If you love creating macros, you may have stored several macros in your workbook. While it is definitely convenient, the issue may arise when you run out of easy shortcuts you can use to call the macro.

Instead of overriding pre-existing shortcuts, you can create macro buttons in Excel that run your macros when you click on them. This can not only be a pretty handy and accessible tool for you to use personally but, also a cool feature to show off to your colleagues.

Inserting buttons can also make your sheet more user-friendly and is a great way to fool-proof tasks in Excel.

Creating Macro Buttons in Excel

Excel offers two button controls: Form and ActiveX. In Windows, you can use both ways to create Macro buttons. However, if you’re a Mac user, you’re limited to just Form Controls. Similarly, you can also assign macros to shapes.  

Make sure you’ve already created your macro before setting up the button. You can either record your macro or modify an existing macro using VBA from the developer tab.

Assign Macro to a Shape

A simple, yet effective way to create a macro button is to assign macros to a shape. You can insert any shapes, including circles, rectangles, diamonds, and many more then assign them a macro. When you click on these shapes, Excel will run the assigned macro.

1. Go to the Insert tab.
2. From the illustrations section, select Shapes.
   
3. Click on the shape you wish to place on your sheet.
   
4. Hold and drag the cursor to create your shape.
5. Right-click on your shape and select Assign Macro.
   
6. Choose your macro and click OK.

Use Form Controls

Form controls are built into Excel, therefore you don’t have to manually add the sheet to your Trust Center Settings. In short, you don’t have to enable macros when you open the sheet as Excel trusts the macros created using the Form Controls.

1. Go to the Developer tab.
2. From the Controls section, select Insert.
3. Under the Form Controls section, choose the first icon.
   
4. Your cursor will change into a plus (+). Click and drag to create your button.
   
5. Select your macro from the Assign Macro window.
6. Click OK.

Use ActiveX Controls

While ActiveX is quite more customizable than Form Controls, you will have to manually place it in your Trust Center Settings. Additionally, ActiveX controls aren’t currently available for you if you’re a Mac user.

1. Head on to the Developer tab.
2. Click Insert from the Controls section.
3. Select the first icon from the ActiveX section.
   
4. Hold, then drag the plus cursor to create your button.
5. Once the button is placed, double-click on the command button.
   
6. In the VBA editor, enter the name(s) of your macro inside the command.
7. Close the VBA editor.
8. From the Controls section, exit Design Mode.
   

Change the Appearance of Macro Buttons

The default formatting of the control buttons isn’t the best to look at. It’s just a plain grey button with black text. If your sheet is following a theme, you may want to change the formatting of your buttons.

Format Shapes with Assigned Macro

1. Select your shape.
2. Go to the Shape Format tab from the menubar.
3. Format the shape using the tools in the Shape Styles section.
   
4. To edit the text, use the tools in the WordArt Styles section.
   
5. Change the height and width of your shape from the Size section.

Format ActiveX Control Buttons 

1. Go to the Developer tab.
2. Click on Design Mode to enable it from the Controls section.
3. Choose Properties.
   
4. Select the fly-out next to BackColor to choose a different fill color for the button.
   
5. To change the font color, select the fly-out next to ForeColor.
   
6. Close the Properties window.
7. Exit Design Mode.

Format Form Control Buttons

1. Right-click on your control button and choose Format Control.
   
2. From the Format Control window, switch between the tabs to format the appearance of your button.
   
3. Click OK to confirm the changes.