Excel Macro Button: What is it and How to Create One
Jul 31, 2022
It is no secret that macros make an Excel user's life easier. So too, do buttons. So it makes sense that combining the two will help make tasks as simple as possible.
Macro buttons give you the power to run through many steps with a single click, saving you so much time and effort when using Excel.
Let's focus on creating your first button in Excel and assigning a macro to it.
Suggested read: Complete Guide to Insert Pivot Chart in Excel
The Three Types of Macro Buttons for Excel Worksheets
This article will examine the three sorts of Macro buttons you can add to your worksheet.
-
Shapes
-
Form control button
-
ActiveX controls
Using Shapes to Create Macro Buttons
Without a doubt, the best way to build a macro button is by using Shapes. Shapes offer you the broadest range of styling and formatting options, so you can change the style of buttons to look as modern as possible.
Here's how to create a button on your worksheet before assigning a macro to it:
-
Start by drawing a shape on your worksheet. Click the Insert tab, then Shapes, and the Rectangle.
-
Add text to your shape by right-clicking on it and selecting Edit Text.
-
Next, assign your Macro by right-clicking on the border of your shape and clicking Assign Macro.
-
From the Assign Macro dialog box that appears, choose your Macro.
-
Hit OK.
Modifying the Macro Button
If, for whatever reason, you want to choose the shape and amend how it looks, click the CTRL shortcut key and the shape simultaneously in your Excel file. This action will stop the macro VBA code from running and let you change the shape.
Microsoft Excel provides a range of options from the Format dialog box to amend the shape effects, shape style, font color, font size, and more.
Suggested read: Difference Between Excel Workbook and Worksheet
Stopping the Button from Resizing with Cell Changes
This aspect depends on what settings you have in place. However, it is probable that when you maned the size of a row or column the button sits on, the size will also change.
A quick and easy way to prevent that is if you select format control.
Firstly, right-click on your macro button and select Size and Properties.
You will see the Format Shape dialog box appear. From here, select the Properties section and amend your rules within that section.
Using Form Controls to Create Macro Buttons
The second option available is creating macro buttons with a Form Control. This setup is highly similar to the one we discussed, but styling is the main difference between Form Control and Shapes.
Form control buttons don't offer the appearance of getting pressed after clicking, but unfortunately, the design is somewhat archaic.
You can also not amend the color of your form control button either. All you can amend is the font size, type, or color.
Insert a Form Control Button
To include a Form Control button, click on the developer tab and Insert. From here, a drop-down offers you options under Form Controls.
Suggested read: Excel Remainder: How to Get it With the Excel Mod Function
Using ActiveX Controls for Macro Buttons
Finally, we have ActiveX control buttons.
The ActiveX controls don't have any other formatting choices, but you can change the button color.
An ActiveX control button also forces you to add an event macro to your sheet module, which will run after the button clicks.
You can include other event triggers, including double click, which could perform a different action if that button is clicked twice.
HOT TIP - Placing Buttons in the Ribbon or Toolbar Instead
One great way to keep the macro buttons handy is to assign a macro dialog box to your quick access toolbar buttons within the Excel Ribbon.
How to Select Assign Macro Buttons: Summary and Key Takeaways
In our opinion, keep it as simple as possible and continue to use shapes as your Macro buttons. Once you use assigned macro buttons that are too complex, it can be tricky to keep up.
Having shapes Macro assigned offers the most formatting options and can be quick and easy to create and amend.
What will you assign macros for? Be sure to put your new knowledge into practice by creating some new Macro buttons, but be careful not to erase existing Macro buttons in the process!
Related Articles:
How to Delete Sheets in Excel: Deleting Multiple Sheets at Once
Want to Make Excel Work for You? Try out 5 Amazing Excel Templates & 5 Unique Lessons
We hate SPAM. We will never sell your information, for any reason.
