assign a macro

• 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?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

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

That’s it!

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

Leave a Comment Cancel reply

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

• Ablebits blog
• Excel macro

How to run macro in Excel and create your own macro button

In this tutorial, we'll cover many different ways to run a macro in Excel - from the ribbon and VB Editor, with a custom keyboard shortcut, and by creating your own macro button.

Though running an Excel macro is a simple thing for experienced users, it might not be immediately obvious to beginners. In this article, you will learn several methods to run macros, some of which may completely change your way of interacting with Excel workbooks.

How to run a macro from Excel ribbon

One of the fastest ways to execute VBA in Excel is to run a macro from the Developer tab. If you have never dealt with VBA code before, you may need to activate the Developer tab first. And then, do the following:

Tip. If the Developer tab is not added to your Excel ribbon, press Alt + F8 to open the Macro dialog.

Run a macro with custom keyboard shortcut

If you execute a certain macro on a regular basis, you can assign a shortcut key to it. A shortcut can be added while recording a new macro and to an existing one. For this, carry out these steps:

• On the Developer tab, in the Code group, click Macros .

• For lowercase letters, the shortcut is Ctrl + letter .

• Close the Macro dialog box.

Tip. It is recommended to always use uppercase key combinations for macros ( Ctrl + Shift + letter ) not to override the default Excel shortcuts. For example, if you assign Ctrl + f to a macro, you will lose the ability to call the Find and Replace dialog.

How to run macro from VBA Editor

If you aim to become an Excel pro, then you should definitely know how to start a macro not only from Excel, but also from the Visual Basic Editor. The good news is that it's a lot easier than you might expect :)

• Press Alt + F11 to launch the Visual Basic Editor.
• In the Project Explorer window on the left, double-click the module containing your macro to open it.
• On the menu bar, click Run > Run Sub/UserForm .
• On the toolbar, click the Run Macro button (green triangle).

Alternatively, you can use one of the following shortcuts:

• Press F5 to run the entire code.
• Press F8 to run each code line separately. This is very useful when testing and debugging macros.

Tip. If you like operating Excel from you keyboard, this tutorial may come in handy: 30 most useful Excel keyboard shortcuts .

How to create a macro button in Excel

The traditional ways of running macros are not hard, but still might present a problem if you are sharing a workbook with someone who has no experience with VBA - they simply won't know where to look! To make running a macro really easy and intuitive for anyone, create your own macro button.

• Click anywhere in the worksheet. This will open the Assign Macro dialogue box.

• If the text does not fit in the button, make the button control bigger or smaller by dragging the sizing handles. When finished, click anywhere on the sheet to exit the edit mode.

Tip. You can also assign a macro to an existing button or other Form controls such as spin buttons or scrollbars. For this, right-click the control inserted in your worksheet and choose Assign Macro from the pop-up menu.

Create a macro button from a graphic object

Regrettably, it is not possible to customize the appearance of button controls, because of which the button we created a moment ago does not look very nice. To make a really beautiful Excel macro button, you can use shapes, icons, images, WordArt and other objects.

As an example, I'll show you how you can run a macro by clicking a shape:

• In your worksheet, click where you want to insert the shape object.
• Format your shape-button the way you want. For example, you can change the fill and outline colors or use one of the predefined styles on the Shape Format tab. To add some text to the shape, simply double-click it and start typing.

How to add a macro button to Quick Access Toolbar

The macro button inserted in a worksheet looks good, but adding a button to each and every sheet is time-consuming. To make your favorite macro accessible from anywhere, add it to the Quick Access Toolbar . Here's how:

• Right-click the Quick Access Toolbar and choose More Commands… from the context menu.
• In the Choose commands from list, select Macros .

• Click OK twice to close both dialog windows.

How to put a macro button on Excel ribbon

In case you have a few frequently used macros in your Excel toolbox, you may find it convenient to have a custom ribbon group, say My Macros , and add all popular macros to that group as buttons.

First, add a custom group to an existing tab or your own tab. For the detailed instructions, please see:

• How to create a custom ribbon tab
• How to add a custom group

And then, add a macro button to your custom group by performing these steps:

• Right-click the ribbon, and then click Customize the Ribbon .
• In the list tabs on the right, select your custom group.
• In the Choose commands from list on the left, select Macros .
• In the list of macros, choose the one you wish to add to the group.
• Click the Add button.

• Click OK to save your changes and close the main dialog box.

How to run a macro on opening a workbook

Sometimes you may want to run a macro automatically on opening a workbook, for example, to display some message, run script or clear a certain range. This can be done in two ways.

Run macro automatically by using Workbook_Open event

Below are the steps to create a macro that automatically runs whenever you open a specific workbook:

• Open the workbook in which you want the macro to be executed.
• Press Alt + F11 to open the Visual Basic Editor.
• In the Project Explorer, double click ThisWorkbook to open its Code window.
• In the Object list above the Code window, select Workbook . This creates an empty procedure for the Open event to which you can add your own code like shown in the screenshot below.

For example, the following code will display a welcome message each time the workbook is opened:

Trigger macro on workbook opening with Auto_Open event

Another way to run a macro automatically on workbook opening is by using the Auto_Open event. Unlike the Workbook_Open event, Auto_Open() should sit in a standard code module, not in ThisWorkbook .

Here are the steps to create such a macro:

• In the Project Explorer , right-click Modules , and then click Insert > Module .
• In the Code window, write the following code:

Here's an example of the real-life code that displays a message box on workbook opening:

Note! The Auto_Open event is deprecated and available for backwards compatibility. In most cases, it can be replaced with the Workbook_Open event. For more information, please see Workbook_Open vs. Auto_Open .

Now that you know lots of ways to run a macro in Excel, you just need to choose the one best suited for your needs. I thank you for reading and hope to see you on our blog next week!

You may also be interested in

• Excel macro tutorial for beginners
• How to record a macro in Excel
• How to insert VBA code
• How to enable macros in Excel
• Personal Macro Workbook in Excel
• User-defined functions vs VBA macros: pros and cons

Table of contents

13 comments

I have created some macro buttons on Excel Quick access toolbar. But if I move my Macro.xlam to other folder (change path), although I have already loaded Macro.xlam (by Developer/Excel Add-in), the macro buttons could not run. How could I do that only load the Macro.xlam and run the macro buttons, not depend on the file location? Thank you.

Hi! In order for Excel to find and automatically load your XLAM file, you must tell Excel where the file is located. If you have moved the file to another location, load it again by using the Developer menu.

I wrote a macro to hide certain rows and columns, print a specific selection and then unhide the pertinent columns and save the workbook. All this is attached to a button. When it prints I get blank pages

Range("b1:F117").Select Selection.PrintOut Copies:=1, Collate:=True

Any suggestion regarding what I am doing wrong?

Your request goes beyond the advice we provide on this blog. If you have a specific question about the operation of a function or formula, I will try to answer it.

Is it possible to assign a macro button to a toolbar that can then be opened on any pc? It seems that when I forward my document with the macro buttons added to the toolbar, they disappear when the new user opens the document however the functions are still seen as listed macros in the document.

Please help

Hi! With a usual Excel file, you cannot transfer your toolbar settings to another user.

I need to run a macro to export a document to PDF, but I need to save to a different location each time I run it. Is this a possibility? I would ideally like to link this macro to a button.

Hello! To store the macros you use frequently, I recommend the Personal Macro Workbook. For more information, please visit: Personal Macro Workbook in Excel - make macros available in all workbooks .

How do I format the TEXT in a macro button, say BOLD, or FONT 14,,,etc

Right click/ edit text/ then just make the changes you want

Hi Svetlana!

Thank you for this useful post. I created a macro spreadsheet with keyboard shortcuts. I would like to ask if it is possible to convert those shortcuts into buttons in ribbon? And then have those shortcuts removed? ( I want to share the sheet with others, but don't want them to accidentally press those keys)

Yes, I found :)

Good Afternoon Svetlana,

I am trying to write two separate if statements with a nested LOOKUP so the responses in the relating cells don't return with a column heading nor an #N/A reply.

The first LOOKUP statement is as follows: =LOOKUP(2,1/($O$6:$O$19=Q2),$M$6:$M$19)

If the response is an amount, it lists the last amount, but if there isn't an amount -or the range is blank, I want it to reply with 0.00

The second LOOKUP statement is as follows: =LOOKUP(2,1/(K:K""),K:K)

The response is a date, but if there has not been a payment in the affecting range, I want it to reply with "No Payment Received"

Please help. Regards, Roger

Post a comment

• Learning Resources
• My.SolidWorks
• Subscription Services

Assigning a Macro to a Toolbar Button

You can assign a macro to a button, choose a bitmap image for the button, and place the button on any toolbar.

• With a document open, click Tools > Customize , or right-click in the window border and click Customize .
• In the Customize dialog box, on the Commands tab, under Categories , select Macro .

• In the Customize Macro Button dialog box, under Action , click Browse (...) and open the macro you want to use.
• If necessary, select a Method . Some macros contain multiple methods. Each method defines a particular sequence of actions and commands. When you select a method, only that method runs in the macro.

The SOLIDWORKS software provides bitmap images to use as custom buttons. These are located in install_dir /data/user macro icons .

• Dimension = 16 x 16 pixels
• Color = 256 colors
• Background color = white
• If necessary, type a Tooltip and a Prompt . Prompt is a brief description of the function of the tool that appears in the status bar, in large tooltips, and in Description on the Keyboard or Mouse Gestures tab of the Customize dialog box.
• Click OK . The new macro button appears in the toolbar.

Provide feedback on this topic

SOLIDWORKS welcomes your feedback concerning the presentation, accuracy, and thoroughness of the documentation. Use the form below to send your comments and suggestions about this topic directly to our documentation team. The documentation team cannot answer technical support questions. Click here for information about technical support .

Thank you for your comments. We will contact you if we have questions regarding your feedback.

Sincerely, The SOLIDWORKS Documentation Team

Print Topic

Select the scope of content to print:

We have detected you are using a browser version older than Internet Explorer 7. For optimized display, we suggest upgrading your browser to Internet Explorer 7 or newer.

Web Help Content Version: SOLIDWORKS 2021 SP05 To disable Web help from within SOLIDWORKS and use local help instead, click Help > Use SOLIDWORKS Web Help . To report problems encountered with the Web help interface and search, contact your local support representative. To provide feedback on individual help topics, use the “Feedback on this topic” link on the individual topic page.

Terms of Use  | Privacy Policy  | Personalize Cookie Choices  | Get a Product Demo  | Contact Sales  | Get a Quote  © 1995-2023 Dassault Systèmes. All rights reserved.