How to Assign a Macro to a Button in Excel (Easy Guide)

Although there are several ways to execute a macro in Excel, none of them can be as simple and intuitive as clicking a button.

And for it to function, a button must first have a macro assigned to it.

In this lesson, I’ll demonstrate a few techniques for adding buttons to Excel and then linking macros to those buttons (or shapes). Once complete, the macro VBA code would run as soon as a user clicked the button.

I’ll be using the VBA macro code below for the duration of this lesson, which does nothing more than choose cell A1 on the active sheet, type “Good Morning” in it, and color it red.

Sub GoodMorning()
With ActiveSheet.Range("A1")
    .Value = "Good Morning"
    .Interior.Color = vbRed
End With
End Sub

The VB Editor’s normal module is where the aforementioned VBA code is inserted.

Let’s get right to it and see how you can link this macro to an Excel button or shape!

This instruction explains: 
1. Add a shape and assign a macro to it.
1.1 Keeping Shape Visible When Rows and Columns are Hiding or Resized
2. Make Form Control Button a Macro
3. Give an ActiveX Control Button a Macro.

1. Add a shape and assign a macro to it.

I’ll start by explaining how to assign a macro to a shape, even though there are specific buttons that you can place in the worksheet and then assign the macro to.

This approach is my favourite and my preference above the other two approaches discussed later. A shape (square or rectangle) may be simply inserted and made to resemble a button.

Additionally, because it is a shape, styling it to match your brand’s colours or current formatting is simple.

The steps to insert a shape in Excel are as follows:

1. to choose the Insert tab.
2. Pick Shapes from the Illustrations group.
3. Select the Rectangle option under Shapes. Your cursor will change to a plus sign when this happens.
4. Anywhere on the worksheet may be clicked. By doing this, a rectangle shape will be added to the worksheet.
5. Format the rectangle after resizing it (give it a border, color, shade if you want).

After completing the aforementioned procedures, your worksheet should now include a rectangular shape. We will now attach a macro to this shape.

In this example, I’ve included a rectangular form, but you may substitute any other shape (such as a circle or triangle or arrow). I find that utilising a rectangle is more logical and looks like a button.

Let’s now examine how to give this form a macro.

1. Select the shape you wish to assign the macro to with a right-click.
2. Select “Assign Macro” from the menu options that display. The assign macro dialogue box will be shown.
3. You may view a list of all the macros you have in the worksheet in the Assign Macro dialogue box.
4. Select the Macro name you want to give this shape by clicking it. I’ll select the “GoodMorning” macro in this instance.
5.  OK

I’m done now!

The shape is now associated with the chosen macro.

The hand icon will now appear when the cursor is over the form. This shows that this form may now be clicked.

Now, if you click on the shape, the designated macro will be executed.

To make the shape more comprehensible, you may write any text inside of it, such “Click here to start the macro.” Right-click the shape and select Edit Text to accomplish this. The text box form may now be typed in.

Remember that once the shape has been chosen (you notice a border around the shape that appears when you choose it), you won’t be able to click to execute the macro. To make the shape clickable, use the Escape key or click anywhere in the worksheet.

Also, if you’ve previously assigned the macro to the shape, you won’t be able to pick it using the left mouse button (as it has become clickable and left-click would now execute the macro). In such scenario, choose the form and then hit the left key while holding down the control key.

1.1 Keeping Shape Visible When Rows and Columns are Hiding or Resized

When you insert a shape in Excel, it sits over the cells like a chart or other object.

The shape also follows your changes when you resize or hide the rows or columns that the shape is over.

The shape in the example below is hidden when I hide the column on which it is positioned.

To prevent this from happening, do the following actions:

1. Just right-click the shape.
2. Choose Format Shape.
3. Choose Size and Properties from the Format Shape pane (or dialogue box, if you’re using Excel 2010 or an earlier version)
4. Choose the ‘Don’t move or size with cells’ option under the Properties menu.
5. Close the window (or dialogue box)

Now, the form would remain in place whether you resized the rows and columns or hidden them.

2. Make Form Control Button a Macro

You may rapidly insert a button from a form control (or an ActiveX control, as is demonstrated below) and then assign a macro to it if you don’t care too much about the formatting of the button and are OK with standard grey buttons.

You must have the Developer tab selected in your ribbon for this to function. Here is a thorough step-by-step guide on how to add the developer tab to the Excel ribbon if you don’t already have it.

After the developer tab is displayed, you can easily add a button and give it a macro by following the instructions below:

1. to choose the Developer tab.
2. to choose the Developer tab.
3. Click the Button (Form Control) option under the Form Controls section of the options that display.
4. Anything on the worksheet may be clicked. The ‘Assign Macro’ dialogue box will launch immediately and the button will be inserted wherever you click.
5. You may view a list of all the macros you have in the worksheet in the Assign Macro dialogue box.
6. Choose the Macro name you wish to give this button by clicking it. I’ll select the ‘GoodMorning’ macro in this instance.
7. OK

The aforementioned actions would insert a button that is bound to the chosen macro.

It would first appear as a little button with the word “Button” printed on it. You have the option of changing the button’s design and the wording to whatever you choose (by dragging the edges).

You may drag and drop this item to any location inside the worksheet since it is a worksheet object that is put over the worksheet (like shapes and charts).

You don’t have a lot of control over the formatting when utilising the Form Control button, which is a downside. You cannot, for instance, alter grey to a different hue.

Although you can format a little bit with a Form control button, it’s nothing compared to what you can do with forms.

When you right-click a button and select Format Control, you will see these formatting choices.

By doing this, you may access the Format Control dialogue box and modify the font’s style, colour, size, alignment, and other attributes.

This button’s ability to remain visible when rows and columns are hidden or resized is a plus. Nevertheless, if you changed the row or column over which the button was positioned, its position would alter as well as its height and width.

The following procedures can be used to modify the configuration if you don’t want the button to stay in its current location:

1. To use, right-click the button.
2. the Format Control button
3. the Properties tab by clicking.
4. Choose “Don’t move or size with cells” from the menu.
5. Ok

3. Give an ActiveX Control Button a Macro.

There is an ActiveX control button in addition to the Form Control button, and you can assign a macro to it.

You won’t typically need to use the ActiveX control button, and I advise you to only do so when you are certain that you fully understand what it is and how to use it.

Are you perplexed as to why Form Control and ActiveX buttons are two distinct types of buttons? Although Excel has built-in Form Controls, ActiveX is loaded from a different DLL (Dynamic Link Libraries). As a result, form control buttons are far more durable and dependable than ActiveX buttons. This discrepancy is discussed in greater detail in a post on Stack Overflow.

Also, because of this, ActiveX can occasionally be erratic and glitchy. Despite the fact that I discuss it in this lesson, I don’t advise utilising an ActiveX button and assigning a macro to it.

The steps listed below can be used to insert an ActiveX button and then attach a macro to it:

to choose the Developer tab.

Choose Insert from the Control group.

In the options that appear, in the ActiveX Controls options, click on the Command Button option.

Anywhere on the worksheet may be clicked. By clicking anywhere, the button will be inserted.

The VB Editor backend will open when you double-click the button, allowing you to enter the ActiveX button’s code there.

With ActiveX control, you get a lot more flexibility with a single button. For example, you can specify one macro to be run when you simply click on the button once and another macro when you double-click or even another one when you use the up/down arrow key.

Again, not something you need to be utilizing in your everyday employment.

Another method you might explore (when dealing with buttons/shapes and assigning macros to them) is to add the macro to the Quick Access Toolbar. In this manner, the macro may be launched with a single click and is constantly displayed in the QAT.

I hope this instruction was helpful. Here are some more thorough Excel VBA tutorials if you’re interested in learning VBA.