How you can Assign a Macro to a Button in Excel (Simple Information)

Whereas there are numerous alternative ways to run a macro in Excel, none of these strategies could be as simple and user-friendly as clicking on a button.

And for that to work, it’s essential assign a macro to a button first.

On this tutorial, I’ll present you a few methods to insert a button in  Excel after which assign a macro to that button (or form). As soon as executed, as quickly as a consumer clicks on the button, the macro VBA code could be executed.

For the aim of this tutorial, I shall be utilizing the beneath VBA macro code (which merely selects cell A1 within the energetic sheet and enters the textual content “Good Morning” in it and colours it purple).

Sub GoodMorning()
With ActiveSheet.Vary(“A1”)
.Worth = “Good Morning”
.Inside.Coloration = vbRed
Finish With
Finish Sub

The above VBA code is positioned in an everyday module within the VB Editor

https://trumpexcel.com/wp-content/uploads/2019/11/Adding-VBA-Macro-to-a-regular-module.png” />

Now let’s dive proper in and see how one can assign this macro to a button or form in Excel!

Insert a Form and Assign Macro to that Form

Whereas there are devoted buttons which you could insert within the worksheet after which assign the macro to it, I’ll first cowl assign a macro to a form.

I personally love this technique and like it over the remainder two strategies lined later. You may simply insert a form (sq. or rectangle) and may make it seem like a button.

And because it’s a form, you possibly can simply format it to look excellent along with your present formatting or model colours.

Under are the steps to insert a form in Excel:

Click on the Insert tabhttps://trumpexcel.com/wp-content/uploads/2019/11/Click-the-Insert-Tab.png” />Within the illustrations group, click on on Shapeshttps://trumpexcel.com/wp-content/uploads/2019/11/Click-on-Shapes.png” />In Shapes choices, click on on the Rectangle choice. You’ll discover that your cursor adjustments to a plus iconhttps://trumpexcel.com/wp-content/uploads/2019/11/Click-on-Rectangle-option.png” />Click on wherever on the worksheet. This can insert a rectangle form within the worksheet.https://trumpexcel.com/wp-content/uploads/2019/11/Rectable-Button-in-the-Worksheet.png” />Resize the rectangle and format it (give it a border, coloration, shade if you’d like).

After you could have executed the above steps, you’ll have a rectangle form within the worksheet, and now we are going to assign a macro to this form.

Word that I’ve inserted a rectangle form on this instance, however you possibly can insert no matter form you need (corresponding to a circle or triangle or arrow). I want utilizing a rectangle and it appears like a button and is extra intuitive.

Now let’s see assign a macro to this form.

Proper-click on the form on which you need to assign the macroIn the menu choices that seem, click on on ‘Assign Macro’. This can open the assign macro dialog fieldhttps://trumpexcel.com/wp-content/uploads/2019/11/Click-on-Assign-Macro.png” />Within the Assign Macro dialog field, you will note a listing of all of the macros that you’ve within the workbookClick the Macro title that you just need to assign to this form. On this instance, I’ll click on on the macro named ‘GoodMorninghttps://trumpexcel.com/wp-content/uploads/2019/11/Select-the-macro-from-the-list.png” />Click on on OK

That’s it!

The chosen macro has now been assigned to the form.

Now while you hover the cursor over the form, it’ll present the hand icon. which signifies that now this form has turn out to be clickable.

https://trumpexcel.com/wp-content/uploads/2019/11/The-button-becomes-clickable.png” />

And now if you happen to click on on the form, it’ll run the assigned macro.

You may sort any textual content inside the form to make it extra intuitive (corresponding to ‘Click on right here to run the macro’). To do that. right-click on the form after which click on on Edit Textual content. Now you possibly can sort inside the textual content field form.

https://trumpexcel.com/wp-content/uploads/2019/11/Edit-Text-option-to-add-text-to-the-shape.png” />

Word that you just gained’t have the ability to click on and run the macro when the form has been chosen (i.e., you see a border across the form that seems when you choose it), To make it clickable, hit the Escape key or click on wherever within the worksheet.

Additionally, when you could have assigned the macro to the form already, you will be unable to pick it through the use of the left mouse key (because it has turn out to be clickable and left-click would now execute the macro). In that case, choose the form, maintain the management key after which press the left key.

Retaining Form Seen If you Cover/Resize Rows/Columns

In Excel. while you insert a form, it sits over the cells – like a chart/object.

This additionally has a disadvantage that while you resize or cover rows/columns which have the form over it, the form additionally follows swimsuit.

Within the beneath instance, the form will get hidden once I cover the column on which it’s positioned.

https://trumpexcel.com/wp-content/uploads/2019/11/Shape-Gets-hidden-when-the-column-is-hidden.gif” />

When you don’t need this to occur, observe the beneath steps:

Proper-click on the shapeClick on Format Formhttps://trumpexcel.com/wp-content/uploads/2019/11/Right-click-and-then-click-on-Format-Shape.png” />Within the Format Form pane (or dialog field in case you’re utilizing Excel 2010 or prior variations), choose Measurement and PropertiesIn the Properties choices, choose the choice – ‘Don’t transfer or dimension with cells’https://trumpexcel.com/wp-content/uploads/2019/11/Click-on-Dont-move-or-size-with-cells.png” />Shut the pane (or dialog field)

Now, while you resize rows/columns or cover these, the form would keep instead.

Assign a Macro to Kind Management Button

When you’re not too involved with the formatting of the button and are happy with common grey buttons, you possibly can shortly insert it from kind management (or ActiveX management as proven subsequent) after which assign a macro to it.

For this to work, you’ll need to have the Developer tab in your ribbon. When you don’t have it, here’s a detailed step-by-step tutorial on getting the developer tab within the Excel ribbon.

After getting the developer tab seen, you should use the beneath steps to shortly insert a button and assign a macro to it:

Click on on the Developer tabhttps://trumpexcel.com/wp-content/uploads/2019/11/Click-the-Developer-tab.png” />Within the Management group, click on on Insert.https://trumpexcel.com/wp-content/uploads/2019/11/Click-on-Insert-option-in-the-Developer-tab.png” />Within the choices that seem, within the Kind Controls choices, click on on the Button (Kind Management) choice.https://trumpexcel.com/wp-content/uploads/2019/11/Click-on-Form-Control-button-icon.png” />Click on wherever on the worksheet. This can insert the button wherever you click on and routinely open the ‘Assign Macro’ dialog field.Within the Assign Macro dialog field, you will note a listing of all of the macros that you’ve within the workbookClick the Macro title that you just need to assign to this button. On this instance, I’ll click on on the macro named ‘GoodMorning’https://trumpexcel.com/wp-content/uploads/2019/11/Select-the-macro-from-the-list.png” />Click on on OK

The above steps would insert a button that has the desired macro assigned to it.

By default, it will be a small button with textual content corresponding to ‘Button’ written on it. You may change the textual content to no matter you need and can even change the form of the button (by dragging the perimeters).

Since that is an object that’s positioned over the worksheet (identical to shapes/charts), you possibly can drag and place it wherever within the worksheet.

One disadvantage of utilizing the Kind Management button is that you just don’t have a lot management over the formatting. For instance, you can’t change the colour from grey to one thing else.

Though there’s a little little bit of formatting that you are able to do with a Kind management button, it’s nowhere near what you are able to do with shapes.

You get these button formatting choices while you right-click on the button after which click on on Format Management.

https://trumpexcel.com/wp-content/uploads/2019/11/Right-click-on-the-button-and-then-click-on-format-control.png” />

This can open the Format Management dialog field the place you possibly can change the font sort/coloration, dimension, alignment, and so on.

https://trumpexcel.com/wp-content/uploads/2019/11/Formatting-options-for-a-form-control-button.png” />

One advantage of this button is that it doesn’t cover or resize while you cover the rows/columns or resize them. It will, nevertheless, transfer in case you alter the peak or width or the row/column over which the button is positioned.

In case you don’t need the button to remain instead, you possibly can change the setting by following the beneath steps:

Proper-click on the buttonClick on Format ControlClick on the Properties tabSelect the choice – ‘Don’t transfer or dimension with cells’https://trumpexcel.com/wp-content/uploads/2019/11/Select-do-not-move-or-size-with-cells-options-for-the-form-control-button.png” />Click on Okay

Assign a Macro to an ActiveX Management Button

Other than the Kind Management button, there may be additionally an ActiveX management button to which you’ll be able to assign a macro.

Generally, you gained’t want to make use of the ActiveX management button, and I like to recommend you employ it solely while you fully perceive what it’s and you recognize what you’re doing.

Questioning why we now have two completely different sorts of buttons – Kind Management and ActiveX? Whereas Kind Controls are in-built within the Excel software, ActiveX is loaded from a separate DLL (Dynamic Hyperlink Libraries). This makes Kind management buttons much more sturdy and dependable as in contrast with the ActiveX buttons. You may learn extra about this distinction right here in a put up in StackOverflow.

This additionally, typically, make ActiveX a bit glitchy and unpredictable. So, whereas I cowl it on this tutorial, I don’t suggest utilizing ActiveX button and assign a macro to it.

To insert an ActiveX button after which assign a macro to it, observe the beneath steps:

Click on on the Developer tabIn the Management group, click on on Insert.Within the choices that seem, within the ActiveX Controls choices, click on on the Command Button choice.https://trumpexcel.com/wp-content/uploads/2019/11/Click-on-ActiveX-Control-Command-Button-option-in-the-developer-tab.png” />Click on wherever on the worksheet. This can insert the button wherever you click on.Double-click on the button and it’ll open the VB Editor backend the place you possibly can place the code for the ActiveX button

With ActiveX management, you get much more flexibility with a single button. For instance, you possibly can specify one macro to be run while you merely click on on the button as soon as and one other macro while you double-click and even one other one while you use the up/down arrow key.

Once more, not one thing it’s essential be utilizing in your common work.

An alternative choice you possibly can take into account (when working with buttons/shapes and assigning macros to it) is so as to add the macro to the Fast Entry Toolbar. That manner, you possibly can run the macro with a single click on and it’s all the time seen within the QAT.

Hope you discovered this tutorial helpful. When you’re curious about studying VBA, you possibly can take a look at extra in-depth Excel VBA tutorials right here.

You may additionally like the next Excel tutorials: