assign macro to button on ribbon

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

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

Twitter | Facebook | YouTube | Pinterest | Linkedin

FREE EXCEL E-BOOK

Introduction

Open the sample file, admin sheet, add macro to drop down, change a button macro, add your own macros, find button images, create an add-in.

More Ribbon Resources

Get the Sample File

Related tutorials.

In this tutorial, you'll see how to add your macros to a custom tab on the Excel Ribbon. The My Macros sample file has a custom tab with 10 buttons, and 2 drop down lists. You can choose label text, button images, and which macro each button should run.

The sample file also contains sample macros, to demonstrate how the custom tab works. Later, you can remove the sample macros, and add your own macros to the file.

To add your macros, you'll make changes on the worksheet. You do NOT need to go into the Ribbon CustomUI, or do any Ribbon programming.

NOTE : If you want to create your own custom tabs from scratch, see these pages:

Excel Ribbon -- Getting Started

• Excel Ribbon -- Add Custom Tab

To see how to add your macros to the ribbon, get the sample workbook , and watch this video. Written instructions are below the video.

Video Timeline

• 00:00 Intro
• 00:26 Getting Started
• 02:02 Test the Macros
• 01:36 Add Macro to Drop Down List
• 04:53 Change a Macro Button
• 06:29 Test the Revised Button
• 06:47 Copy Your Macro Code
• 08:50 Find a Button Image
• 11:51 Save As an Add-In
• 13:22 Get the Sample File

Follow these steps to get started with the Custom Ribbon tab.

• First, go to the Download section below, and get the My Macros sample file.
• Due to Microsoft security settings, you might need to unblock the downloaded file. Right-click on the zipped file, click Properties, and then check the box to Unblock.

• Next, unzip the downloaded folder, and store the Excel file in a folder on your computer.
• Open the Excel file, and be sure to enable macros.

Test the Custom Tab

After you open the My Macros sample file, you should see a custom tab on the Excel Ribbon -- MY MACROS. The custom tab has buttons and drop down lists, described below.

There are 10 buttons at the left, in 2 groups - Macro Buttons1 and Macro Buttons2.

To test the macros, click the Hello Message button, at the left side of the MY MACROS tab.

That button runs a macro that shows a message box, with the word, "Hello".

Click the OK button to close the message box.

Drop Down Lists

In the centre of the MY MACROS tab, there are 2 drop down lists -- Macro List 1, and Macro List 2.

Click the drop down arrow on Macro List 1, and in the drop down menu, click ToggleGridlines.

That runs the ToggleGridlines macro, which changes the gridlines setting on the active worksheet.

• If gridlines were off, it shows the gridlines.
• If gridlines were on, it hides the gridlines.

At the right of the MY MACROS tab, there are two command buttons for Excel Help.

Click those buttons to go to the main page of my Contextures website, or to this instruction page for the custom MY MACROS tab.

To run different macros from the MY MACROS Ribbon tab, you will make changes on the Admin sheet in the sample workbook.

The Button information is stored in the table at the left side of the Admin sheet.

In the second row, you can see the settings for Btn2. It has the label "Hello Message", and runs the HelloMsg macro. It displays the image named WebGoBack.

At the right, there are two Excel tables. They contain the macro names for each of the drop down lists.

In Macro List 1, you can see the ToggleGridlines macro, which you tested earlier.

The drop down lists are flexible, and will show all the items from the Macro List tables on the worksheet. You can delete items from the worksheet lists, add new items, or change the existing macro names.

Get Ready to Add a Macro

For the first change, you will add another macro to Macro List 1. Before you add the new macro, take a look at the current list:

• In the MY MACROS tab, click the arrow for Macro List 1 drop down.
• The 3 items in that drop down list are an exact match for the 3 items in the worksheet list for Macro List 1.

Add Another Macro

Next, you'll add another one of the sample macros to the Macro List 1 drop down.

• Click in cell G10, and type the macro name -- ToggleZeros .
• The table will automatically expand, to include the new row.

• On the Ribbon, click the Macro List 1 drop down again -- the Toggle Zeros macro hasn't been added there yet.
• To force the list to update, click on one of the other macros, such as ToggleRefStyle
• Then, click the arrow to see the list again --ToggleZero is now in the list.

There are 10 buttons set up in the MY MACROS tab. They can be customized, but there are some restrictions on the button changes.

• You CAN change the button labels, button images, and the macros that the buttons run.
• Do NOT change the names in the ButtonID column of the worksheet list.
• Do NOT add or delete buttons from the worksheet list

Note : If you want to add or remove buttons, you will have to modify the CustomUI file, to add them. See how to work with the CustomUI file .

Change a Button

For the first change, you will change the settings for Button 3. Currently, that button has an "X" image, and, and runs the TestMsg macro

• Label: Colour List
• Macro: ColourList
• Image: FontColorCycle

• Save the My Macros sample file
• Close the My Macros sample file, and then re-open it.
• Button 3 should now show its new label and image. Click the Colour List button, to add a new sheet to the workbook, with a list of colours.

The My Macros sample file contains a few macros, to show how the custom Ribbon tab works. You can keep some or all of those macros, or delete all of them, and put your own macros into the workbook.

First, copy the macro code that you want to add to the My Macros workbook. (For details on how to copy macro code to a regular module, see these instructions .)

You could use macro code from one of your Excel files, or find sample code on an Excel website. There is sample macro code below ( ListAllNames ), that you can use as a test.

After you copy the macro code, follow these steps, to paste your macros into the code modules:

• In Excel, press Alt+F11, to open the Visual Basic Editor (VBE)
• In the Project Explorer list, find the MyMacrosCustomTab project
• DO NOT change anything on the RibbonMacros module or the MacrosCtx module.
• You can add or remove macros on the ButtonMacros, Macros or Macros2 modules
• You can also insert new modules, and paste your macro code there.

• When you're finished, close the VBE, and return to Excel

Sample Macro Code

(Optional) If you don't have your own macro code to test, copy this macro code, and then follow the instructions above , to add it to the My Macros workbook. This macro adds a new sheet in your workbook, with a list of all the names in that workbook. Or, if there are no names, it shows a message.

Update the Admin Sheet

After you finish adding your macros, go to the Admin sheet and add your macro names.

• You can change the Button list, and replace the sample macro names and labels with your macros. Be sure to use the exact names for the macros that you added to the workbook.
• See the next section, for details on how to find image names to use with your macros
• You can add your macros to either of the Macro List tables

Remember to save your file , after making the changes

In the Button list, an image name is assigned to each of the buttons. After you add your own macros to the workbook, you might want different images, to match the actions in your macros.

Ribbon Commands

Image gallery add-ins.

For the My Macros sample file, I chose simple images, like the arrows and a plus sign. To find those images, I checked the commands in the Customize the Ribbon window. This is a quick and easy way to find built-in images for Excel.

Follow these steps to find different images for your buttons:

• Right-click on a blank part of the Excel Ribbon, and click Customize the Ribbon
• By default, Excel shows a list of Popular Commands
• To see different commands, click the arrow for the "Choose commands from" drop down
• Choose Commands Not in the Ribbon -- I like to use these images, because they're different from the ones that are already in use

• Scroll through the list, to find an image that you want to use for one of your macro buttons
• Then, to find the official name for the image, point to the command, and a popup will appear, with information about that command
• At the end of the command, in round bracket, is the image name

• Make a note of that name, so you can add it to the Button List. You will need to use the exact spelling , so write it down carefully, with the same upper and lower case .
• After you have found all the image names that you need, click the OK button, to close the Excel Options window.
• Then, add those image names, exactly , to the BtnImage column on the Admin sheet.

Another way to find built-in button images is with Image Gallery addins. For example:

• Download and install the 2007 Office System Add-In: Icons Gallery from Microsoft:
• After installing the gallery, open Excel and click the Developer tab.
• A new group appears at the right end of the tab, with 9 galleries.
• Point to any icon, to see its name.
• You can use that name when setting up your custom Ribbon tab. Be sure to use the exact spelling, with the same upper and lower case letters.

The My Macros sample file is saved in xlsm format, and its custom Ribbon tab is only visible when the sample workbook is active. If you want to use your macros in any open workbook, follow these steps to save the file as an Excel add-in.

Save File as Add-In

You can create your own add-ins, by storing macros in a file, then saving it as an add-in:

• Record or create one or more macros in the file
• On the Excel Ribbon, click the File tab, and click Save As
• Click the Browse button, to open the Save As window (stay in the selected folder)
• At the bottom, type a File Name
• In the Save As Type drop down, select Excel Add-In (*.xlam)
• The AddIns folder should be automatically selected
• Click Save, then close Excel
• To use your add-in file, follow the steps below, to install it

Install Your Add-in

After you have saved your file as an add-in, follow these steps to install it.

• Open Excel, and on the Ribbon, click the Developer tab (if it's missing, follow these instructions to show it)
• Click the Add-ins button.

• In the Add-in dialog box, find the My Macros Custom Ribbon Tab add-in, and add a check mark to its name.

• Click OK, to close the Add-ins window.
• The custom tab -- MY TOOLS -- should appear on the Ribbon.
• Click the MY TOOLS tab, and use the buttons and drop down lists to run your macros

Make Changes to Your Add-in

When you save a file as an Add-in, all of its worksheets are automatically hidden. If you want to make changes to the button settings or the macro lists, follow these steps:

• Click the + beside Microsoft Excel Objects, to see the objects
• Click on ThisWorkbook, to see its properties in the Properties window
• In the Properties list, scroll down to find the IsAddin property
• Change the IsAddin property to False

• Go back to Excel, and the workbook's sheets will be visible again
• Make your changes to the settings on the Admin sheet

NOTE : You will not be able to save the workbook while you make these changes. Go to the next section to see how to save.

Save the Add-in Changes

WARNING : When you close Excel, you might not get the usual warning, asking if you want to save your changes. After your changes are completed, follow these steps to save your changes.

• Change the IsAddin property to True
• Click the Save button at the top of the VBE window
• Close VBE, and go back to Excel

Excel Ribbon Resources

Thanks to the following people, who have shared their knowledge about Excel Ribbon customization.

• Ron de Bruin, Excel MVP, for the helpful Ribbon customization information on his website
• Ken Puls, Excel MVP, and co-author of RibbonX: Customizing the Office 2007 Ribbon - a great reference book.
• Greg Maxey, Word MVP, who posted Ribbon customization examples for Microsoft Word. There is a download link for his sample file, in the blue paragraph near the end of this page

To try the Ribbon custom macros tab, get the My Macros sample file . The zipped file is in xlsm format, and contains macros. After you add your macros, and change the button settings, you can save the file in xlam format, to create your own Macros add-in.

Excel Ribbon - Custom Tab

Customize Quick Access Toolbar (QAT)

Create a UserForm With ComboBoxes

VBA Code, Copy to a workbook

Last updated: November 20, 2023 12:12 PM

• 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

17 comments

I have created a "click here to submit" button, but when the users open the document and fill out the form, once they hit submit and it transfers the document in to an email back to me, the document comes over blank. I'm not sure how to fix this as I've redone the document entirely and the same thing happens.

Hi! We do not do VBA code creation or customization on request. In addition, I am not able to see the code that is in your workbook.

I need my Macro to do several things, use an Ablebits feature, and then do several more things. But I can't seem to get it to record the Ablebits step.

Is there a way with VBA to include the Ablebits steps? Or, even better, a way to turn on recording them?

Hello! Unfortunately, you can't use Ablebits feature in VBA code.

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

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.

• PRO Courses Guides New Tech Help Pro Expert Videos About wikiHow Pro Upgrade Sign In
• EXPLORE Tech Help Pro About Us Random Article Quizzes Request a New Article Community Dashboard This Or That Game Popular Categories Arts and Entertainment Artwork Books Movies Computers and Electronics Computers Phone Skills Technology Hacks Health Men's Health Mental Health Women's Health Relationships Dating Love Relationship Issues Hobbies and Crafts Crafts Drawing Games Education & Communication Communication Skills Personal Development Studying Personal Care and Style Fashion Hair Care Personal Hygiene Youth Personal Care School Stuff Dating All Categories Arts and Entertainment Finance and Business Home and Garden Relationship Quizzes Cars & Other Vehicles Food and Entertaining Personal Care and Style Sports and Fitness Computers and Electronics Health Pets and Animals Travel Education & Communication Hobbies and Crafts Philosophy and Religion Work World Family Life Holidays and Traditions Relationships Youth
• Browse Articles
• Learn Something New
• Quizzes Hot
• This Or That Game
• Train Your Brain
• Explore More
• Support wikiHow
• About wikiHow
• Log in / Sign up
• Computers and Electronics
• Spreadsheets
• Microsoft Excel

How to Create a Custom Macro Button in Excel

Last Updated: July 28, 2022

wikiHow is a “wiki,” similar to Wikipedia, which means that many of our articles are co-written by multiple authors. To create this article, 13 people, some anonymous, worked to edit and improve it over time. This article has been viewed 485,924 times. Learn more...

Macros in Excel can save a great deal of time with repetitive tasks. By assigning macros to custom buttons, you can save even more time by bringing your macro only one-click from execution.

• Click File → Options → Customize Ribbons
• Find the Developer check box in the Main Tabs section and click it. Press "OK" when you are done.

• Go to Excel → Preferences → Ribbon (Under Sharing and Privacy)
• Under Customize, check the box next to the Developer tab, and press "OK"

• If you are unfamiliar with what macros are or how to record them, read more. You should have a macro already built before you create the button.

Expert Q&A

• Try using the 2003 method for Excel versions earlier than 2003. Thanks Helpful 0 Not Helpful 0
• Alternatively, you may add your macro button to an existing toolbar in versions 2003 and earlier. Thanks Helpful 0 Not Helpful 0
• If you prefer, it will let you assign a shortcut key in the dialog box. This can prevent wrist strain and save time. Thanks Helpful 0 Not Helpful 0

• The user interface on versions earlier than 2003 may be different so the 2003 method may not be exactly the same for those versions. Thanks Helpful 2 Not Helpful 0
• If you want a different button image than what version 2007 offers, you will need to download additional software that specializes in modifying user interfaces for Microsoft Office. Thanks Helpful 2 Not Helpful 0

You Might Also Like

• ↑ http://office.microsoft.com/en-us/excel-help/add-a-button-and-assign-a-macro-to-it-in-a-worksheet-HP010342137.aspx

About This Article

• Send fan mail to authors

Is this article up to date?

Featured Articles

Trending Articles

Watch Articles

• Terms of Use
• Privacy Policy
• Do Not Sell or Share My Info
• Not Selling Info

wikiHow Tech Help Pro:

Level up your tech skills and stay ahead of the curve

Excel Tutorial: How To Add Macro Button In Excel

Introduction.

Have you ever found yourself performing the same repetitive tasks in Excel? If so, you may benefit from incorporating macro buttons into your spreadsheets. A macro button is a clickable object that, when pressed, executes a series of pre-recorded commands. This can save you time and effort by automating tasks that you perform frequently. In this tutorial, we will discuss the importance of having macro buttons in Excel and provide a step-by-step guide on how to add them to your spreadsheets.

Key Takeaways

• Macro buttons in Excel can save time and effort by automating repetitive tasks.
• Understanding macros and their benefits is important for efficient spreadsheet management.
• Creating and assigning a macro in Excel involves recording, writing code, and customizing appearance.
• Accessing the Developer tab and inserting a macro button on the ribbon are crucial steps in the process.
• Testing and using the macro button ensures that the assigned macro works as intended.

Understanding Macros in Excel

A macro in Excel is a set of instructions that automate tasks. It is a series of commands and actions that can be stored and executed to perform a specific task or set of tasks in Excel. Macros can be created using the Visual Basic for Applications (VBA) language, which allows users to write code to automate repetitive tasks.

Using macros in Excel can provide several benefits, including:

• Time-saving: Macros can automate repetitive tasks, saving time and reducing the likelihood of errors.
• Increased accuracy: By automating tasks, macros can help ensure that calculations and data manipulations are consistently and accurately applied.
• Customization: Macros can be customized to suit specific needs and workflows, allowing users to create personalized solutions for their Excel tasks.
• Efficiency: Automating tasks with macros can make processes more efficient, freeing up time for more complex tasks.

Adding a Macro Button in Excel

Once you have created a macro in Excel, you may want to add a button to the Excel interface to easily execute the macro. This can be done by following these steps:

• Create or open an Excel workbook: Start by creating a new workbook or opening an existing one in which you have created a macro.
• Access the Developer tab: If the Developer tab is not already visible in the Excel ribbon, you can enable it by going to File > Options > Customize Ribbon, and then checking the Developer option.
• Insert a button: Once the Developer tab is visible, click on it and select the "Insert" option. Choose the button control that you want to add to the workbook.
• Assign the macro: After inserting the button, Excel will prompt you to assign a macro to it. Select the macro that you want to assign to the button from the list provided.
• Position and format the button: You can position and format the button as needed to make it easily accessible and user-friendly.
• Test the button: Once the button is in place, test it to ensure that it executes the macro as intended.

Creating a Macro in Excel

Macros in Excel can help automate repetitive tasks, saving time and effort. There are two main methods for creating macros in Excel: recording a macro and writing a macro code.

Recording a macro in Excel is a simple way to automate a series of actions. Follow these steps to record a macro:

• Step 1: On the Developer tab, click on "Record Macro."
• Step 2: Enter a name for the macro and choose a shortcut key if desired.
• Step 3: Select where to store the macro (in the current workbook or in the Personal Macro Workbook).
• Step 4: Click "OK" to start recording your actions.
• Step 5: Perform the actions you want to automate.
• Step 6: Click on "Stop Recording" on the Developer tab when you're done.

B. Writing a macro code

If you're comfortable with VBA (Visual Basic for Applications) programming, you can write a macro code to customize the functionality of your macro. Here's how to write a macro code in Excel:

• Step 1: On the Developer tab, click on "Visual Basic" to open the VBA editor.
• Step 2: In the VBA editor, insert a new module for your macro code.
• Step 3: Write the VBA code to perform the actions you want to automate.
• Step 4: Close the VBA editor and return to Excel.
• Step 5: Now you can assign the macro to a button or a shape in your Excel worksheet for easy access.

Whether you choose to record a macro or write a macro code, adding a macro button in Excel can greatly improve your productivity by automating repetitive tasks.

Adding a Macro Button to the Excel Ribbon

Microsoft Excel allows users to create and run macros to automate repetitive tasks. By adding a macro button to the Excel ribbon, you can easily access and execute your macros. Here’s a step-by-step guide on how to do it:

In order to add a macro button to the Excel ribbon, you first need to access the Developer tab within Excel. Follow these steps to enable the Developer tab:

• Step 1: Open Excel and click on the “File” tab.
• Step 2: Select “Options” from the left-hand menu.
• Step 3: In the Excel Options window, click on “Customize Ribbon” in the left-hand menu.
• Step 4: Check the box next to “Developer” in the right-hand pane and click “OK” to enable the Developer tab.

Once the Developer tab is enabled, you can proceed to insert a macro button onto the Excel ribbon. Here’s how to do it:

• Step 1: Click on the “Developer” tab in the Excel ribbon.
• Step 2: In the “Controls” group, click on the “Insert” drop-down button.
• Step 3: From the drop-down menu, select “Button” under the “Form Controls” section.
• Step 4: Click and drag on the worksheet to insert the button at the desired location.
• Step 5: In the “Assign Macro” dialog box, select the macro you want to assign to the button and click “OK”.

Once you have completed these steps, the macro button will be added to the Excel ribbon, allowing you to easily execute the assigned macro with a single click.

Assigning a Macro to the Button

Adding a macro button to your Excel workbook can greatly improve the efficiency of your workflow. Once you have created a macro, you can easily assign it to a button for quick access. Here's how to do it:

Testing and Using the Macro Button

After you have created and added a macro button in Excel, the next step is to test and use the button to ensure that it works as intended. This involves clicking the button to run the macro and ensuring that the desired actions are executed.

Once you have added the macro button to your Excel workbook, you can simply click on the button to run the macro. The button should be located in a visible and accessible location within the workbook, making it easy for users to find and use.

After clicking the button to run the macro, it is important to ensure that the macro performs the desired actions accurately. This may involve checking for the completion of specific tasks, such as data manipulation, formatting, or calculations. It is crucial to thoroughly test the macro to ensure that it produces the expected results.

Using macro buttons in Excel can greatly improve your workflow by automating repetitive tasks and increasing efficiency. It's a powerful tool that can save you time and effort in the long run.

We encourage you to practice and utilize macro buttons in your Excel work. The more you familiarize yourself with this feature, the more you'll be able to take advantage of its benefits and streamline your work processes.

Immediate Download

MAC & PC Compatible

Free Email Support

Related aticles

The Benefits of Excel Dashboards for Data Analysts

Unlock the Power of Real-Time Data Visualization with Excel Dashboards

Unlocking the Potential of Excel's Data Dashboard

Unleashing the Benefits of a Dashboard with Maximum Impact in Excel

Exploring Data Easily and Securely: Essential Features for Excel Dashboards

Unlock the Benefits of Real-Time Dashboard Updates in Excel

Unleashing the Power of Excel Dashboards

Understanding the Benefits and Challenges of Excel Dashboard Design and Development

Leverage Your Data with Excel Dashboards

Crafting the Perfect Dashboard for Excel

An Introduction to Excel Dashboards

How to Create an Effective Excel Dashboard

• Choosing a selection results in a full page refresh.

How to Use Excel VBA to Customize Ribbon (with Easy Steps)

Sometimes a user might need a custom ribbon if he wants to have quick access to the macros and commands he uses frequently. Excel allows users to customize the ribbon according to their needs easily. In this article, we will walk you through five easy steps in Excel VBA to customize ribbon without using any third-party software.

How to Launch VBA Editor in Excel

• To access the Microsoft Visual Basic window, go to the Developer tab and then click on Visual Basic . You can also open it by pressing Alt+F11 on your keyboard.

• Then go to the Insert tab and click on Module to open the code Module .

Excel VBA to Customize Ribbon: 5 Easy Steps

In this article, we will demonstrate five quick and easy steps in Excel VBA to customize ribbon without installing any additional software. We will use the Windows Notebook for this purpose.

Step 1: Save Excel File as Zip File

In the first step, we will change the extension of the Excel file. We have a sample Excel file that has xlsm extension. We want to change it to zip .

• First, click on the View panel and tick the checkbox of File name extensions .
• As a result, the file extension will be visible in the file name.
• Rename the file by changing the extension to zip .

• Double-click the zip file to see the folders inside it. The zip file should contain _rels , docProps , and xl folders.

Step 2: Create an XML File to Add New Groups, Tabs, and Buttons

In this step, we will create an XML file to add groups, tabs, and buttons.

• Create a new folder and name it as you like. We named our folder Custom .

• Inside the folder, create a text file and change the extension to xml . Here, our created file is Custom_Rib.xml .

• Then, open the file with Notepad .

• After that, paste the following code. This code will create a custom tab. The custom tab has two groups. Both of the group contains two buttons.

• Save the file by pressing Ctrl+S .
• Finally, drag and drop the Custom folder to the zip file to complete this step.

Read More: How to Add Custom Ribbon Using XML in Excel

Step 3: Edit .rels File and Add Relationship to Excel File

To customize the ribbon in Excel VBA , we will modify the .rels file to add a relationship with the Custom file to the sample Excel file.

• First, copy the _rels folder from the zip file.

• Next, paste it into any folder outside the zip file.

• Then go to the _rels folder and open the .rels file with Notepad .

• Inside the file, add a new Relationship and change the Target argument. The argument must match the folder and custom XML file name.

• Then save the .rels file.
• After that, Delete the existing _rels folder inside the zip file.
• Finally, drag and drop the new _rels folder in the zip file.

• Now, to see the newly added tab, groups, and buttons, change the file extension of the zip file to xlsm file.

• Finally, launch the xlsm file and you will find a new tab named Custom Tab that contains two groups and four buttons.

Step 4: Customize Buttons with Icon

To enhance the appearance of the buttons, we will now add icons to them. You can add built-in icons or you can add any image you want as the icon.

• First, create two new folders inside the Custom folder and name them _rels and Images .

• The image folder contains three image files with png extension.

• In the _rels folder, create a new file and name it Custom_Rib.xml.rels .

• Open the file with Notepad and paste the following code.

• After that, open Custom_Rib.xml file and insert the following code there.
•  imageMso = “HappyFace” adds a built-in icon to Button 1 .
•  image = “icon_2” adds the image that has icon_2 as Id to Button 2 .  

Step 5: Connect Macros to Buttons

In the last step, we will connect to the buttons. The code for connecting the macro is given below.

This onAction connection is known as Callbacks . Creating a sub-procedure using Macro Name will assign the code to the button.

• After adding all the codes, save the files.
• Then delete the existing Custom folder from the zip file and add the new Custom folder inside it.
• Now change the extension of the zip file to xlsm and open the Excel file.
• Click on Custom Tab and you will find that all the buttons have images and icons.

• To test the buttons, write the following sample code in a VBA code Module . The code will work when Button 4 is clicked.

Code Breakdown:

• This line takes a range in an InputBox and assigns it to myRange variable.   
• myRange.Delete deletes the content of myRange .
• Click on Button 4 and an InputBox will pop up.
• Select a range in the InputBox and as a result, the range will be deleted.

Frequently Asked Questions

1. What is the shortcut to customize ribbon?

First, press Alt+F and then press T to open the word option dialogue box. After that, press the Down Arrow key and select Customize Ribbon .

2. How do I personalize a ribbon in Excel?

Right-click on the Excel ribbon and select Customize the Ribbon . From the Ribbon tab window, you can add or remove different commands, tabs, or groups as per your need.

Things to Remember

• Before dragging a folder in the zip file, delete the existing folder with the same name inside the zip file.
• While changing the extension of a file, you will get a warning message. You can simply click on Yes to continue when that happens.

Download Practice Workbook

Download this practice workbook to exercise while reading this article.

In this article, we demonstrated five easy steps to create and edit Excel VBA to customize ribbon without using any third-party software. We used Notebook to create an XML file and then added a relationship to the Excel file. In this article, we have created a new custom tab that has two groups. Each group contains two buttons. Finally, we have shown how to assign macros to these buttons to make them work. Thanks for reading this article. We hope you found this article useful. If you have any queries or recommendations regarding this article, feel free to let us know in the comment section below.

Related Articles

• How to Display Developer Tab on the Ribbon in Excel
• How to Restore Ribbon in Excel
• How to Add Data Types to Excel Ribbon

<< Go Back to Customize Excel Ribbon | Excel Ribbon  |  Excel Parts  |  Learn Excel

What is ExcelDemy?

Tags: Customize Excel Ribbon

Alif Bin Hussain earned a BSc in Civil Engineering from Bangladesh University of Engineering and Technology. As an engineering graduate, he has a deep passion for research and innovation. He loves to play with Excel. In his role as an Excel & VBA Content Developer at ExcelDemy, he not only solves tricky problems but also shows enthusiasm and expertise in handling tough situations with finesse, emphasizing his dedication to delivering top-notch content. He is interested in C, C++,... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF

Contribute to the Microsoft 365 and Office forum! Click  here  to learn more  💡

April 9, 2024

Contribute to the Microsoft 365 and Office forum!

Click  here  to learn more  💡

Word Forum Top Contributors: Stefan Blom  -  Charles Kenyon  -  Doug Robbins - MVP Office Apps & Services (Word)  -  Suzanne S. Barnhill  -  Bob Jones AKA: CyberTaz   ✅

May 10, 2024

Word Forum Top Contributors:

Stefan Blom  -  Charles Kenyon  -  Doug Robbins - MVP Office Apps & Services (Word)  -  Suzanne S. Barnhill  -  Bob Jones AKA: CyberTaz   ✅

• Search the community and support articles
• Microsoft 365 and Office
• Search Community member

Ask a new question

How to add custom macro buttons with custom icons to the Ribbon/QAT.

HOW TO ADD CUSTOM MACRO BUTTONS

WITH CUSTOM ICONS TO THE RIBBON/QAT

** Important: never have the document that you're working on open in the Custom UI Editor and Word at the same time! This can and will cause problems, such as losing the custom XML code and having to redo it. **

  1. Open a new document in Word and save it as a Word macro-enabled document (.docm).

  2. Close Word.

  3. Open the Custom UI Editor (see download link at the end of this post).

  4. Open the Word document that you created in step one.

  5. Click Insert on the menu and select "Office 2010 Custom UI Part" or "Office 2007 Custom UI Part" (choose the one that corresponds to the Office version that you're using).

  6. Click on the "Insert icons" button on the Custom UI Editor toolbar to add the images that you want to use on the macro buttons. The ideal size for button images that are going to be added to the QAT (Quick Access Toolbar) is 16 pixels x 16 pixels. The following types of images can be used for b utton icons: PNG, JPG, BMP, WMF, ICO and GIF (my attempts to use ICO images have been unsuccessful).

  7. In the right-hand pane, add the following XML code:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">

  <ribbon>

   <tabs>

     <tab id="Icons" label="Images" visible="true">

       <group id="Images" label="Icon Images">

<button id="Btn1" label="Open AAA" image="AAA"

             onAction="RBX.ButtonOnAction"/>

          <button id="Btn2" label="V Line" image="vertical"

        onAction="RBX.ButtonOnAction"/>

<button id="Btn3" label="V Lines" image="vertical2"

         onAction="RBX.ButtonOnAction"/>

  </group>

    </tab>

  </tabs>

  </ribbon>

</customUI>

The above code creates a custom tab on the Ribbon with a group containing three buttons with custom icons. This custom Ribbon tab will be used to "store" the custom buttons/images that are going to be placed on the QAT . You can type in a name for the custom tab in the "label" attribute that comes after “tab id”. Use the groups “label” attribute to provide a name for the group.

  8. Enter the name (id) of the icon image (as it appears next to the icon image in the Custom UI Editor) that you want to use for each button in the buttons "image" attribute. Use the buttons "label" attribute to specify the text that you want to be displayed when the mouse pointer hovers over the button.    

  9. Click on the "Validate" button to verify that the code doesn't contain errors.

10. Click the Save button (or press Ctrl-S) and exit the Custom UI Editor.

11. Reopen the document in Word.

12. Press Alt-F11 to open the Visual Basic Editor.

13. In the Project pane, click on the Project that has the document name next to it.

14. Select Insert > Module from the menu and rename the module "RBX" by typing the new name in the box next to (Name) in the Properties pane.

15. Double-click on the "RBX" module.

16. In the code pane (below where it says "General"), add the following VBA code:

Sub ButtonOnAction(Control As IRibbonControl)

  Select Case Control.ID

        Case "Btn1"

    NewMacros.Macro10

        Case "Btn2"

    NewMacros.Macro3

        Case "Btn3"

    NewMacros.Macro4

  End Select

Enter each buttons id (obtained from the XML code) after the Case statement and enter the name of the macro that you want the button to run on the next line.

Note that you have to include the Module name ("NewMacros" in the example) followed by a dot and then the macro name for the macro buttons to function.

17. Save the changes and close the Visual Basic Editor.

18. Right-click on the Ribbon or QAT and select "Customize Quick Access Toolbar".

19. Click on the box below where it says “Choose commands from” and then click on the name of the custom Ribbon tab that you created in step 7.

20. Click on one of the custom macro buttons that you want to add to the QAT.

21. Click the "Add" button to add the button to the QAT.

22. Repeat steps 20 and 21 to add additional custom macro buttons to the QAT.

23. Use the arrow buttons on the right side to reposition the buttons on the QAT, if needed.

24. Click "Ok".

Note: If you want to hide the custom Ribbon tab after you're done adding custom buttons to the QAT you can change the "visible" attribute in the XML code to "false". This doesn't affect the custom buttons on the QAT, they'll still remain visible on the QAT.

- If you want the custom buttons to be available for all documents, do one of the following two options:

  Option 1: Save the customizations to the Normal template. This isn't recommended for various reasons, the main reason being that the Normal template is prone to corruption in which case it has to be deleted which means you lose your customizations.

  Option 2 (recommended): Save the Word Macro-Enabled Document (.docm) file that you created in step 1 as a Word Macro-Enabled Template (.dotm) file by doing the following:

1. Open the file that you created in step 1.

2. Press F12.

3. Select “Word Macro-Enabled Template (*.dotm)” from the “Save as” drop-down menu.

4. Click on “Save”.

5. Place a copy of the .dotm file in Word’s startup folder (keep the original file as a backup).

- When you save a Word Macro-Enabled Document file (.docm) as a Word Macro-Enabled Template file (.dotm) the macros and the customizations must be stored in the same template file (.dotm) in order for the custom macro buttons on the QAT to remain functional.

- Double-clicking on a Word template file in File Explorer opens a new blank document based on the template and not the actual template. To open a template file for editing and/or customizing, right-click on the template and select open.

Office Custom UI Editor download link:

http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2010/08/10/23248.aspx

Source of the XML and VBA code:

https://answers.microsoft.com/en-us/office/forum/office_2010-word/word-2010-edit-icons-on-quick-access-toolbar/336b850d-67db-4c00-8021-ee65ee4f360f

Other useful resources:

http://addbalance.com/word/startup.htm

https://gregmaxey.com/word_tip_pages/customize_ribbon_main.html       

https://gregmaxey.com/word_tip_pages/modify_qat_button_image_text.html

https://gregmaxey.com/word_tip_pages/add_macro_to_qat.html

https://docs.microsoft.com/en-us/previous-versions/office/developer/officetalk2007/cc508991(v=office.11)#additional-resources

Another useful resource is the “RibbonX: Customizing the Office 2007 Ribbon” book published by Wiley Publishing, Inc.

49 people found this helpful

Report abuse

Reported content has been submitted​

Was this discussion helpful? Yes No

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this discussion?

Thanks for your feedback, it helps us improve the site.

Thanks for your feedback.

Replies (2) 

• Volunteer Moderator |
• Article Author

Adding the button to the QAT is much simpler in the Word Interface.

Modifying the QAT in Microsoft Word

Unlike ribbon modification in the UI, you can store your modifications in a document or template.It is important, IMO, to store the modification in the same template/document that contains the macro. That document or template must be open, not just loaded, for you to use it as a storage location.

Then, if you want, modify the icon using XML.

Modify Quick Access Toolbar (QAT) Button Image or Text

Also, there is a big difference between a .docm and a .dotm file. You seldom want to use the former for a QAT modification because a .docm file will not automatically load as a global template (because it is not a template).

1 person found this reply helpful

Was this reply helpful? Yes No

How satisfied are you with this reply?

I agree that adding a button to the QAT is much simpler using the Word interface. But Word 2016 doesn't provide a way to add custom images to a button on the QAT using the Word interface.

After trying many different methods/approaches (including the links in your post, which are very helpful and informative) and experiencing many failed attempts to create custom macro buttons with custom images I finally came across the following page containing the solution I was looking for:

In earlier versions of Word it was possible to create a custom icon for a button in less than 5 minutes via the Word UI. Apparently, Microsoft decided to eliminate this feature in order to reduce help desk calls and to streamline the UI (ie, maintain a standard button layout and appearance).   

4 people found this reply helpful

Discussion Info

• Norsk Bokmål
• Ελληνικά
• Русский
• עברית
• العربية
• ไทย
• 한국어
• 中文(简体)
• 中文(繁體)
• 日本語

Assign a macro to a button

You can use a button (a form control) to run a macro that performs an action when a user clicks it. For example, you might use a button to automate the printing of a worksheet, the filtering of data, or the calculation of numbers.

After you create a macro , you can assign it to a button you click to run the macro . You can assign a macro to a button on the Quick Access Toolbar or to a button in your own personal group on the ribbon.

If you want a macro button to be available in other workbooks, assign it to a macro that was created in a personal workbook .

Add a macro button to the Quick Access Toolbar

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 move the macro to the list of buttons on the Quick Access Toolbar.

To replace the default macro icon with a different button for your macro, click Modify .

Under Symbol , select a button icon for your macro.

To use a friendlier name for the button, in the Display name box, enter the name you want.

You can enter a space in the button name.

Click OK twice.

The new button appears on the Quick Access Toolbar, where you can click it to run the macro.

Tip:  When you save the workbook , buttons you assign to macros in the personal workbook will be available in every workbook you open.

Add a macro button to your own group on the ribbon

Click File > Options > Customize Ribbon .

Under Customize the Ribbon , in the Main Tabs list , check the Developer box if it is not already checked.

Pick the tab where you want to add your own group.

For example, pick Home , to add your group to the Home tab.

Select New Group .

That adds New Group (Custom) to the tab you picked.

To use a better name for your new group, click Rename , type the name you want in the Display name box, and then click OK .

You can enter a space in the name. For example, type My Macros .

To add a macro to the group, in the Choose commands from list, click Macros .

Select the macro you want to add to your new group, and then click Add . The macro is added to the My Macros group.

To use a friendlier name, click Rename , and then type the name you want in the Display name box.

You can enter a space in the name.

Your new group appears on the tab you picked, where you can click the button to run the macro.

On the Developer tab, in the Controls group, click Button .

If the Developer tab is not available

Go to Excel > Preferences... > Ribbon & Toolbar .

In the Customize the Ribbon section, under Main Tabs , check the Developer check box, and press OK.

Click the worksheet location where you want the upper-left corner of the button to appear.

In the Assign Macro dialog box, click the name of the macro that you want to assign to the button, and then click OK .

To resize the button, drag the sizing handles.

To specify the control properties of the button, Control+Click or right-click the button, and then click Format Control .

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.