There are three different types of Excel Macro Buttons.

• Shapes
• Form Controls
• ActiveX Controls

Using Shapes as Excel Macro Button

The best way to create macros buttons is to use shapes to develop better-stylized buttons similar to modern webpages, which will help with the user experience.

Draw a Shape

• Click on Insert Tab
• Click on Shapes Icon
• Click on the required shape type. There are numerous options to choose from:
  • Rectangles
  • Basic Shapes
  • Block Arrows
  • Equation Shapes
  • Flowchart
  • Stars and Banners
  • Callouts
• On selection, you can drag in a suitable location in Excel Sheet.

Change Shape Color

• Click on created Button.
• Click on Shape Format Tab, which will appear after you’ve selected the button.
• After that, you can click on any suitable color desired.

Assign Macro to Shape

• Right-click anywhere on the Button.
• Click on Assign Macro then a window will pop up asking for the macro name.

• Select the required macro, if available. If not, you can write the desired name.
• Click on New, and this will then take you to Visual Basic Window to update your macro.

Advanced Shape Properties

• Right Click on the Button
• Click on Size and Properties…
• If you want to change the Shape properties, Click on Shape Options. If you want some advanced Text styling, Click on Text Options.
• You have three options for Shape Options.
  • Fill & Line
  • Effects
  • Size & Properties
• Based on the Selected option below window marked as 5 will change.

Format Shape window is high configurable, and you can do many advanced effects like Shadow, Glow, etc., explaining that this window would take many more posts as I would need to clarify in a lot of depth.

Developer Tab

Now I’m going to explain Form and ActiveX Buttons. For that, we’ll need to have Developer Tab. If it is not available in your Ribbon, you can follow the below steps to display it in your Ribbon.

• You can click on any blank area in the Ribbon.
• Click on Customize the Ribbon…
• Then it will take you to the Excel Options window.

• Customize Ribbon should be already selected. If not, you can select the same.
• Click on the CheckBox to activate Developer, as shown in the above image.

Using Form and ActiveX as Excel Macro Button

Both of these buttons are essentialy same in terms of assinging macros, etc. The only difference is with regards to styling and other properties.

• Click on Developer tab.
• Then click on the Insert button.
• Now you can see pop up with Form Controls and ActiveX Controls.
• We have a lot of controls here like Button, List, Checkbox, Radio, Text, etc.
• We are only interested in the first option, which is Button for now.
• Once you click on the Form Button / ActiveX Button, you can then Drag it the same in Excel wherever required.

Form Button

This button type has minimal functionality in terms of styling. This essentially looks like early version of Windows. We can change only the text formatting of the button in Font Type, Size, Color. I would recommend to avoid using this button type as it doesn’t help with user experience.

ActiveX Button

The ActiveX Control buttons do have more formatting options, and you can change the button color. They also require you to add an event macro in the sheet module that will run when the button is clicked. This can be done by right-clicking the button and choosing View Code.

However, I don’t recommend the ActiveX Control buttons. In my experience, they are glitchy in terms of consistent appearance. They can end up big or small when changes are made to the sheet or when the file is shared back and forth between users.

Also, ActiveX Button has many more properties than the Form Button, as shown in the above image.

Conclusion

My recommendation is to use Shapes as your Excel Button as they have many advanced formatting techniques if you so need it. The other button types have their limitation, and You can use Form and ActiveX Buttons only if you have no choice.

If you have any other queries related to Excel, please comment. I’ll make sure to answer them.