- Work with macros Video
- Edit a macro Video
- Save your macro Video
- Assign a button to a macro Video
Assign a button to a macro
In this final video, we’ll hook the macro up with a button and make it available in two places: In a custom group on the Developer tab, and on the Quick Access Toolbar.
Create or delete a macro
Edit a macro
Assign a macro to a button
Create and save all your macros in a single workbook
In this final video, we'll hook the macro up with a button and make it available in two places.
In a custom group on the ribbon that I'll create on the DEVELOPER tab, I'll call My Macros , and up here on the Quick Access Toolbar .
First, I'll create a custom group for the DEVELOPER tab.
I click the FILE tab, Options , Customize Ribbon , and then I make sure Developer is checked.
I click New Group and here is our new group.
I click Rename , and call it My Macros .
Now, let's find the macro and add it to this custom group.
I go over here and choose commands from Macros .
I select the "FillDaysRelativeR" macro that's stored in my 'Personal Workbook' and click Add .
Here it is, it's now in the group and I want to rename it.
I click Rename and call this "Fill Days".
You can have spaces in display names.
While I am at it, I'll replace the cryptic icon with this icon that looks a little bit like a calendar.
I click OK , click OK again and here's my macro button in my custom My Macro group.
I click it and it works.
Now I'll show you how to add the same macro to the Quick Access Toolbar , so it'll be up here next to Redo .
Click FILE and Options , and this time, click Quick Access Toolbar .
Again, I choose commands from Macros, and here's the macro in my personal workbook. Click Add .
To rename it, click Modify . Call it "Fill Days" and choose the same grid icon.
I click OK , and click OK again.
Now here it is, up on the Quick Access Toolbar .
I click it, and of course, it works great again.
Now you have a pretty good idea about how to implement macros in Excel.
Of course, there's always more to learn.
So check out the course summary at the end, and best of all, explore Excel 2013 on your own.
Need more help?
Expand your skills.
EXPLORE TRAINING >
Get new features first
JOIN MICROSOFT 365 INSIDERS >
Was this information helpful?
Thank you for your feedback.
How to Assign a Macro to a Button in Excel
March 07, 2018
Running macros in your worksheet is a tremendous help. You can automate so many things with VBA that there have been countless books written on the topic. However, it’s not always so simple to run a macro. Your users may not know how to run a macro and you want to make it easy for your users to get their work done. Wouldn’t it be great to have a button on your worksheet and you can tell your users “Just click on this and such-and-such will be done automatically”?
That’s what this post is all about. Let’s get started.
Assign a Macro to a Simple Button
To begin, you’ll need to have the Developer Tab enabled. Click here for more info on that.
After you have the developer tab enabled, open up the Visual Basic Editor, add a new module and let’s add this simple code snippet in there:
This will be our simple macro to run when we click on our button.
Since we have the Developer Tab enabled, we can create a button and assign it to a macro:
- In the Developer Tab of the ribbon, simply click on Button (in Windows, click on Insert, and then under Form Controls, click on Button)
- Then click on the Worksheet to add the button.
- Excel will then ask you to assign it to a macro. Use the WriteHello macro that we put in our VBE.
- The button will then be in Edit mode. This is where you can change the text inside it. Make the text whatever you like. You can also resize the button while it is in edit mode.
- Then click on the worksheet away from the button to get it out of edit mode. You are now able to click on your button and run the macro!
Here’s an animated gif to help illustrate the point:
Does this article help you? If so, please consider supporting me with a coffee ☕️
Assign a Macro to a Shape
Now that we have the main idea down, let’s add some style to our worksheet. Using the Button from the Developer Tab is not the only way to run a macro from a button. You can also use Excel Shapes!
To achieve this, we follow a similar procedure from before, but the difference is where we get our button from. Here are the steps to assign a macro to a shape in your worksheet:
- Click on the Insert tab in the ribbon
- Click on Shapes
- Select a Shape (I used a rounded rectangle)
- Click and drag on the worksheet to set the size of the shape
- You can then write some text. You can also format this text by selecting it and going to the Home tab and adjusting the text size, center justify, and center vertically.
- Then, right-click on the Shape and select Assign Macro.
- Pick the macro that we wrote.
- Then click on the worksheet to get the shape out of edit mode.
- You can now click on your shape (which is now a button) and run your macro!
Here’s an animated gif to show more detail:
Changing the Assigned Macro
To change the macro assigned to the button, simply right-click on it, then choose Assign Macro. From there, you can select a new macro to use.
Deleting the Button
To delete the button, you need to get it back into Edit Mode. To do this, right-click on the button. When you do this, the context menu will show up. You can click back to the worksheet (but DON’T double-click!) and you will still be in Edit Mode. From here, you can hit the Delete key on your keyboard and it will remove the button.
How to Assign a Macro to a Button in Excel (Easy Guide)
- -- By Sumit Bansal
While there are many different ways to run a macro in Excel, none of those methods can be as easy and user-friendly as clicking on a button.
And for that to work, you need to assign a macro to a button first.
In this tutorial, I will show you a couple of ways to insert a button in Excel and then assign a macro to that button (or shape). Once done, as soon as a user clicks on the button, the macro VBA code would be executed.
For the purpose of this tutorial, I will be using the below VBA macro code (which simply selects cell A1 in the active sheet and enters the text “Good Morning” in it and colors it red).
The above VBA code is placed in a regular module in the VB Editor
Now let’s dive right in and see how you can assign this macro to a button or shape in Excel!
This Tutorial Covers:
Insert a Shape and Assign Macro to that Shape
While there are dedicated buttons that you can insert in the worksheet and then assign the macro to it, I will first cover how to assign a macro to a shape .
I personally love this method and prefer it over the rest two methods covered later. You can easily insert a shape (square or rectangle) and can make it look like a button.
And since it’s a shape, you can easily format it to look perfect with your existing formatting or brand colors.
Below are the steps to insert a shape in Excel:
- Resize the rectangle and format it (give it a border, color, shade if you want).
After you have done the above steps, you will have a rectangle shape in the worksheet, and now we will assign a macro to this shape.
Now let’s see how to assign a macro to this shape.
- Right-click on the shape on which you want to assign the macro
- In the Assign Macro dialog box, you will see a list of all the macros that you have in the workbook
- Click on OK
The selected macro has now been assigned to the shape.
Now when you hover the cursor over the shape, it will show the hand icon. which indicates that now this shape has become clickable.
And now if you click on the shape, it will run the assigned macro .
You can type any text within the shape to make it more intuitive (such as ‘Click here to run the macro’). To do this. right-click on the shape and then click on Edit Text. Now you can type within the text box shape.
Note that you won’t be able to click and run the macro when the shape has been selected (i.e., you see a border around the shape that appears when you select it), To make it clickable, hit the Escape key or click anywhere in the worksheet.
Also, when you have assigned the macro to the shape already, you will not be able to select it by using the left mouse key (as it has become clickable and left-click would now execute the macro). In that case, select the shape, hold the control key and then press the left key.
Keeping Shape Visible When you Hide/Resize Rows/Columns
In Excel. when you insert a shape, it sits over the cells – like a chart/object.
This also has a drawback that when you resize or hide rows/columns that have the shape over it, the shape also follows suit.
In the below example, the shape gets hidden when I hide the column on which it’s placed.
If you don’t want this to happen, follow the below steps:
- Right-click on the shape
- In the Format Shape pane (or dialog box in case you’re using Excel 2010 or prior versions), select Size and Properties
- Close the pane (or dialog box)
Now, when you resize rows/columns or hide these, the shape would stay in its place.
Assign a Macro to Form Control Button
If you’re not too concerned with the formatting of the button and are ok with regular gray buttons, you can quickly insert it from form control (or ActiveX control as shown next) and then assign a macro to it.
For this to work, you will need to have the Developer tab in your ribbon. If you don’t have it, here is a detailed step-by-step tutorial on getting the developer tab in the Excel ribbon .
Once you have the developer tab visible, you can use the below steps to quickly insert a button and assign a macro to it:
- Click anywhere on the worksheet. This will insert the button wherever you click and automatically open the ‘Assign Macro’ dialog box.
The above steps would insert a button that has the specified macro assigned to it.
By default, it would be a small button with text such as ‘Button’ written on it. You can change the text to whatever you want and can also change the shape of the button (by dragging the edges).
Since this is an object that is placed over the worksheet (just like shapes/charts), you can drag and place it anywhere in the worksheet.
One drawback of using the Form Control button is that you don’t have much control over the formatting. For example, you can not change the color from gray to something else.
Although there is a little bit of formatting that you can do with a Form control button, it’s nowhere close to what you can do with shapes.
You get these button formatting options when you right-click on the button and then click on Format Control.
This will open the Format Control dialog box where you can change the font type/color, size, alignment, etc.
One good thing about this button is that it doesn’t hide or resize when you hide the rows/columns or resize them. It would, however, move in case you change the height or width or the row/column over which the button is placed.
In case you don’t want the button to stay in its place, you can change the setting by following the below steps:
- Right-click on the button
- Click on Format Control
- Click on the Properties tab
Assign a Macro to an ActiveX Control Button
Apart from the Form Control button, there is also an ActiveX control button to which you can assign a macro.
In most cases, you won’t need to use the ActiveX control button, and I recommend you use it only when you completely understand what it is and you know what you’re doing.
This also, sometimes, make ActiveX a bit glitchy and unpredictable. So, while I cover it in this tutorial, I don’t recommend using ActiveX button and assign a macro to it.
To insert an ActiveX button and then assign a macro to it, follow the below steps:
- Click on the Developer tab
- In the Control group, click on Insert.
- Click anywhere on the worksheet. This will insert the button wherever you click.
- Double-click on the button and it will open the VB Editor backend where you can place the code for the ActiveX button
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 using in your regular work.
Hope you found this tutorial useful. If you’re interested in learning VBA, you can check out more in-depth Excel VBA tutorials here .
You may also like the following Excel tutorials:
- How to Record a Macro in Excel
- Creating a User Defined Function (UDF) in Excel VBA
- Excel VBA MsgBox [Message Box]
- Useful Excel Macro Examples for VBA Beginners
- How to Remove Macros From an Excel Workbook
- How to Enable Macros in Excel?
FREE EXCEL BOOK
Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster
1 thought on “how to assign a macro to a button in excel (easy guide)”.
Following the procedure here, I cannot assign a Macros from an *.xlam workbook (addin). Is there a way to do this? thx
Comments are closed.
BEST EXCEL TUTORIALS
Best Excel Shortcuts
Creating a Pivot Table
INDEX- MATCH Combo
Creating a Drop Down List
Recording a Macro
© TrumpExcel.com – Free Online Excel Training
Add a Button and Assign a Macro in Excel
In Excel, Buttons are used to call Macros. This tutorial will cover how to create Excel buttons, assign Macros to them, adjust their properties, and more.
By default, Excel macros are accessible in a list via the “Macros” button on the View ribbon.
Often though, you’ll want to provide easy access to a particular macro directly on your worksheet. This can be achieved using a Button control.
A Button control looks like a Microsoft Windows button, and runs a macro when clicked. It’s a much handier way to access your most commonly used macros, and is an easy way to expose custom functionality to other users of your workbook.
Run a Macro From a Button
To run a Macro from a button in Excel, simply click the button:
The Excel Developer Tab
Buttons are accessible via the Developer Tab.
Unfortunately, Excel hides the Developer tab by default. If you don’t see the Developer Ribbon, follow these steps:
- Click File > Options in the list on the left-hand border
- In the Options dialog select C ustomize Ribbon > Customize the Ribbon > Main Tabs and add a check-mark in the box for “Developer”, and click OK .
Add a Macro Button
In Excel, select the Developer tab, then click on the “ Insert ” dropdown in the Controls section. There are several types of controls divided into two sections, “Form Controls” and “ActiveX Controls”.
For now, just click on the Button control under “ Form Controls ”. Next, move the mouse anywhere over the worksheet surface, then hold left-click and drag the mouse to draw the outline of a rectangle. When you release left-click, a new dialog will appear titled “ Assign Macro ”.
Assigning a Macro to a Button
Here you can assign an existing Macro to the button, record a new macro, create a new macro from scratch using VBA, or click “Cancel” and return to your button later.
Assign Existing Macro to a Button
To assign an existing Macro, you simply select the macro’s name in the list, then click OK.
Edit an Existing Macro Before Assigning to a Button
To edit a macro before assigning it to the button, select the macro’s name in the list and click the “Edit” button (the “New” button text changes to “Edit”).
Record a Macro and Assign to Button
Write VBA Procedure and Assign to Button
To write a new macro for the button, type a new name for your macro in the textbox at the top of the dialog, then click “New”. Excel will bring up the VB Editor, in which you’ll see a new empty macro procedure with the name you entered. This procedure will be stored in a new module, visible in the Project window.
Change Macro Assigned to Button
To change the Macro that’s assigned to a button, simply right-click the button and select Assign Macro :
Here you can see the assigned Macro and make any desired changes.
How to Adjust Button Properties in Excel
Move or resize excel button.
After you’ve placed a button, you can easily move or resize it. To perform any of these actions, right-click on the button. Then you can left-click and drag the button to your desired location or resize it.
With the button selected, left-click on the button text to edit.
To add multiple lines, simple press the ENTER key.
To format other button properties, Right-Click > Format Control
Here you can adjust font sizes, and many other button properties:
Of particular note is the “Properties” tab, which changes how the button behaves as surrounding rows and columns are inserted, deleted, resized, or hidden/unhidden.
- Move and size with cells : The button will move and resize when rows and columns are changed.
- Move but don’t size with cells : The button will move, but not resize.
- Don’t move or size with cells : The button will not move or resize.
- Finally, Print Object can set the object to appear on printouts. This is unchecked by default, but can be toggled on if desired.
Assign a Macro to a Shape
Besides buttons, macros can assigned to other objects like Pictures, Textboxes, and Shapes. With a Picture or Shape, you can make a button that looks any way you like. Excel includes a wide variety of customizable Shapes including polygons, arrows, banners, and more that may be better suited to your worksheet than a regular button control.
Shapes are accessed from the Insert tab:
Select the shape you want from the Shape dropdown, draw it onto your worksheet as you would a button control, then right-click it and select “Assign Macro…” from the pop-up dialog. The options are the same as assigning a macro to a button.
Assign a Macro to a Hyperlink
Macros can also be assigned to hyperlinks by using VBA Events . Events are procedures that are triggered when certain actions are performed:
- Open/Close/Save Workbook
- Activate / Deactivate Worksheet
- Cell Values Change
- Click Hyperlink
Events require knowledge of VBA. To learn more about events, visit our VBA Tutorial .
Ultimate VBA Add-in
Offer expires soon!
Sale Ends Friday at Midnight
VBA (Macros) Code Examples Add-in
#1 Excel tutorial on the net
- Create a Macro
Developer Tab | Command Button | Assign a Macro | Visual Basic Editor
With Excel VBA you can automate tasks in Excel by writing so called macros. In this chapter, learn how to create a simple macro which will be executed after clicking on a command button. First, turn on the Developer tab.
To turn on the Developer tab, execute the following steps.
1. Right click anywhere on the ribbon, and then click Customize the Ribbon.
2. Under Customize the Ribbon, on the right side of the dialog box, select Main tabs (if necessary).
3. Check the Developer check box.
4. Click OK.
5. You can find the Developer tab next to the View tab.
To place a command button on your worksheet, execute the following steps.
1. On the Developer tab , click Insert.
2. In the ActiveX Controls group, click Command Button.
3. Drag a command button on your worksheet.
Assign a Macro
To assign a macro (one or more code lines) to the command button, execute the following steps.
1. Right click CommandButton1 (make sure Design Mode is selected).
2. Click View Code.
The Visual Basic Editor appears.
3. Place your cursor between Private Sub CommandButton1_Click() and End Sub.
4. Add the code line shown below.
Note: the window on the left with the names Sheet1 (Sheet1) and ThisWorkbook is called the Project Explorer. If the Project Explorer is not visible, click View, Project Explorer. If the Code window for Sheet1 is not visible, click Sheet1 (Sheet1). You can ignore the Option Explicit statement for now.
5. Close the Visual Basic Editor.
6. Click the command button on the sheet (make sure Design Mode is deselected).
Congratulations. You've just created a macro in Excel!
Visual Basic Editor
To open the Visual Basic Editor, on the Developer tab , click Visual Basic.
Learn more, it's easy
- Swap Values
- Run Code from a Module
- Macro Recorder
- Use Relative References
- Add a Macro to the Toolbar
- Enable Macros
- Protect Macro
Download Excel File
Follow Excel Easy
Become an Excel Pro
- 300 Examples
Create a Macro • © 2010-2023 Excel is Awesome, we'll show you: Introduction • Basics • Functions • Data Analysis • VBA
Add a button (Form control) · On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Button · Click the worksheet location
Click File > Options > Quick Access Toolbar. · In the Choose commands from list, click Macros. · Select the macro you want to assign a button to. · Click Add to
Assign a button to a macro ... In this final video, we'll hook the macro up with a button and make it available in two places: In a custom group on the Developer
Assign a Macro to a Simple Button · In the Developer Tab of the ribbon, simply click on Button (in Windows, click on Insert, and then under Form
In this video, I demonstrate how to assign a macro to a button in Excel. The video runs you through how to create a simple navigation button
In this micro-lesson you will:- Click on the desired location for the button or draw a box with the mouse- Select the desired Macro from the
http://www.TeachMsOffice.comThis video tutorial shows you how to attach or assign a macro in excel to a button, check box, or any other
Insert a Shape and Assign Macro to that Shape · Right-click on the shape on which you want to assign the macro · In the menu options that appear
To change the Macro that's assigned to a button, simply right-click the button and select Assign Macro: Here you can see the assigned Macro and make any desired
Create a Macro · 1. On the Developer tab, click Insert. · 2. In the ActiveX Controls group, click Command Button. Insert a command button control · 3. Drag a