Need advanced Excel, VBA or SQL support? Outsource your project now!
Connect to our Experts within seconds for a free diagnosis
Our professional experts are available now. Your privacy is guaranteed.

How to Add A Macro Button in Excel – Excelchat

We can create macro button in excel to perform a wide range of functions whenever we click on the macro button. In this tutorial, we will explore the ways to create macro buttons from shapes, command buttons, Form control buttons and the use of ActiveX Control buttons.

Figure 1 – How to create a button in Excel

Making Excel Macro Buttons for Worksheets

We can easily create three types of buttons in excel for our worksheet. This includes:

  1. Shapes
  2. Form Control Buttons
  3. ActiveX Control Buttons

Creating Macro Buttons with Shapes

We can use shapes to create macro buttons. This is perhaps the best because it offers many ways to style and format our buttons. To begin:

 

  • We will pick a Shape on the Insert Tab, by clicking Insert, then Shapes and maybe the Rectangle shape

 

Figure 2 –  How to insert button in excel

  • We will add text inside the shape by either Right-clicking and selecting Edit Text or simply double-clicking on the shape.

Figure 3 – Creating macro buttons in Excel

  • We will right-click on the border of the shape and assign macro

Figure 4 – How to Make a button in Excel

  • Now, we will select macro from the list but we must always select the macro from This Workbook list so that when we share a workbook with others, our macros will also be shared
  • We will select OK

Figure 5 – Using Excel button to run the macro

  • We can easily click on the shape at any time to run a macro

How to modify excel buttons created with shapes

We can change the shape of our macro by holding on the Ctrl key while clicking on our button. We will immediately see the Format Tab where we can change the font color, shape, style, and size of effects.

How to ensure that the button does not resize with cell changes

  • We will Right-click on the button
  • We will select Size and Properties

Figure 6 – Formatting button in Excel

  • We will choose our options in the Format Shape pane that will pop-up.

Figure 7 – Resize or modify Excel macro buttons

How to add a button in Excel using Form Controls in Windows

  • We will go to the Developer Tab and select Insert
  • In the Drop-down menu, we will click on Form Controls

Figure 8 – How to assign a macro to a button in Excel

  • We will tap Button and assign Macro pop to the area we want to see it
  • We will click OK. We can equally control the properties of the button by right-clicking on it and then tapping Format Control

Figure 9 – Excel buttons to run macros

Insert a macro button in Mac

  • First, we will enable the developer mode by following these steps:
    • We will click on File, then Options
    • Under the Customize Ribbon, we check the box beside Developer and click Save
  • Now we add the form Control button, by selecting on the Developer

Figure 10 – Excel Options dialog box

    • Next, we tap a button and pick the location on our worksheet within the Assign Macro pop-up that appears

 

  • We can equally control our button properties by right-clicking and selecting Format Control

 

Making Macro Buttons in Excel using ActiveX or Command button (Visual Basic Control)

  • We will click Button on the Developer Tab

Figure 11 – Add button in Excel

  • We will select the location we want the command button to appear

 

  • We will assign macro by using clicking New, which takes us to the Visual Basic Editor.

 

  • In this place, we will enter the VBA below:

Sub ButtonX_Click()

End Sub

  • Between the Sub and End Sub Lines, we will use any of these steps 
    • Enter the name of the existing macro in the workbook. We can run different macros from one button by entering the names on separate subprocedures
    • Alternatively, we can write our own VBA code

 

  • Next, we will click on Design Mode to ensure that this is Switched off and close the VBA window

 

Figure 12 – Using Command buttons in Excel

  • We can easily format button by right-clicking on it and select Format Control

Figure 13 – Make a button in Excel

Note

Using ActiveX Controls carries a downside. We have more formatting options, but they have inconsistent appearances at most. They can become very small or really big at any time.

Explanations

When we have an Excel Macro button on our screen, it becomes better to switch quickly into the VBA mode. By using Macros and its buttons, we can have a more satisfying and ease of using and making our spreadsheets.

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc