Upgrade Now

Excel Macro Button: What is it and How to Create One

Aug 01, 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 a ton of steps with a single click, which will save you so much time and effort when using Excel.

Let's focus on how to create your first button in excel and assign a macro to it.

The Three Types of Macro Buttons for Excel Worksheets

In this article, we will look at the three different 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 widest 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, 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 would like to choose the shape and amend how it looks, click the CTRL shortcut key and the shape at the same time 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, so you can amend the shape effects, shape style, font color, font size, and more.

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 of the button will change as well.

There is a quick and easy way to prevent that from happening 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 to you is to create macro buttons with a Form Control. This setup is extremely similar to the one we have discussed, but the main difference between Form Control and Shapes is the styling.

Form control buttons don't offer the appearance of getting pressed after getting clicked, but unfortunately, the design is somewhat archaic in style.

You are also unable to 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 click insert. From here, a drop-down offers you options under Form Controls.

Using ActiveX Controls for Macro Buttons

Finally, we have ActiveX control buttons.

The ActiveX controls don't have any further 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 gets clicked.

You have the power to 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 at all times 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 start to use assigned macro buttons that are too complex, it can be tricky to keep up.

Having shapes macro assigned offers you 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

Microsoft Excel Certification: How to Become a Professional

Free Microsoft Excel Tutorial For Beginners

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.