add macro button to ribbon excel 2016

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.

Edit Text option to add text to the 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

Right click and then click on Format 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.

Formatting options for a form control button

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

Select do not move or size with cells options for the form control button

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 on ActiveX Control Command Button option in the developer tab

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

Excel VBA: Adding custom Button to the Toolbar or Ribbon

Custom buttons are added to the toolbars or ribbons when a Microsoft Excel sheet is opened, and removed when the sheet is closed. To be notified when a given Excel sheet is loaded we need to listen to the Workbook_Open event of the ThisWorkbook object found in the VBA Project explorer.

Please notice that the following code works best with Excel versions prior to Office 2007. If used in newer versions, from Excel 2007, the button and commandbar will be added to the “Add-Ins” tab. It is only possible to add buttons to the main ribbon using dynamic XML when using VBA. See below for more information.

The custom event called OnDoSomething must be defined in a global Module . It doesn’t work to define the method in the Workbook class. Insert a new Module and add the following code:

You can change the button icon by specifying another FaceId value. To get a list of all available FaceIds on you computer, download and and install the FaceID Browser:

FaceID Browser

Excel 2007 and later To dynamically add buttons to the Ribbon you must use a combination of XML and VBA. For more information, please visit:

How to: Use VBA to Add a Custom Command to the Ribbon
Excel VBA: Adding items to the right-click menu
Get Microsoft Excel

One thought on “ Excel VBA: Adding custom Button to the Toolbar or Ribbon ”

Pingback: Excel VBA: Adding custom buttons to the Right-Click popup menu - Coragi

Attendance Tracker Template for Microsoft Excel

Employees and their managers often need to know how many vacation days they have left, how many sick days they’ve used, and whether or not they have personal days left. The user-friendly attendance tracker template from Coragi makes it possible for you to record attendance as well as a summary of key data for a […]

Employee Absence tracking Template for Microsoft Excel

From time to time, every employee will be absent from work. Companies must deal with sick leave in some form or another for a variety of reasons. Absence might range from a minor cold that clears up in a few days to a lengthy sick leave that has a significant influence on a team’s performance […]

Excel VBA: Get Employee information from Active Directory

If you manage people in your organization, either as a leader or a project manager, the Microsoft Active Directory (AD) is a gold mine of valuable information open to anyone inside the corporate network. Active Directory (AD) is a directory service for Windows domain networks. It was original designed to keep track of computers and […]

Privacy Overview

Ablebits blog

Customize Excel ribbon with your own tabs, groups or commands

See how to customize Excel ribbon with your own tabs and commands, hide and show tabs, rename and rearrange groups, restore the ribbon to the default settings, back up and share your custom ribbon with other users.

Introduced in Excel 2007, the ribbon allows you to access most of the commands and features. In Excel 2010, the ribbon became customizable. Why would you want to personalize the ribbon? Perhaps you will find it convenient to have your own tab with your favorite and most used commands at your fingertips. Or you will want to hide tabs you use less often. Whatever the reason, this tutorial will teach you how to quickly customize the ribbon to your liking.

Excel ribbon: what can and cannot be customized

Before you start doing something, it's always good to know what can and what cannot be done.

What you can customize

To save your time and efforts when working on different tasks in Excel, you can personalize the ribbon with things like:

Show, hide, and rename tabs.
Rearrange tabs, groups and custom commands in the order you want.
Create a new tab with your own commands.
Add and remove groups on existing tabs.
Export or import your personalized ribbon.

What you can't customize

Though a lot of ribbon customizations are allowed in Excel, certain things cannot be changed:

You can neither change nor remove the built-in commands, including their names, icons and order.
You cannot resize the ribbon, nor can you change the size of the text or the default icons. However, you can hide the ribbon completely or collapse it to show only the tab names.
You cannot change the color of the ribbon in Excel, but you can change the color scheme of the entire Office.

How to customize ribbon in Excel

Most customizations to the Excel ribbon are done in the Customize the Ribbon window, which is part of Excel Options . So, to start customizing the ribbon, do one of the following:

Go to File > Options > Customize Ribbon .

Either way, the Excel Options dialog window will open enabling you to do all the customizations described below. The instructions are the same for Excel 2019, Excel 2016, Excel 2013 and Excel 2010.

How to create a new tab for the ribbon

To make your favorite commands easily accessible, you can add your own tab to the Excel ribbon. Here's how:

Select the newly created tab, named New Tab (Custom) , and click the Rename… button to give your tab an appropriate name. In the same manner, change the default name given by Excel to a custom group. For the detailed guidelines, please see how to rename ribbon items .

Tips and notes:

By default, a custom tab is placed after the currently selected tab (after the Home tab in our case), but you are free to move it anywhere on the ribbon.
Each tab and group that you create have the word Custom after their names, which is added automatically to distinguish between built-in and custom items. The word ( Custom ) only appears in the Customize Ribbon window, not on the ribbon.

How to add a custom group to a ribbon tab

To add a new group to either a default or custom tab, this is what you need to do:

In the right part of the Customize the Ribbon window, select the tab to which you'd like to add a new group.

Click OK to save and view your changes.

Tip. To save some room on the ribbon, you can remove text from the commands in your custom group and show only the icons.

How to add a command button to Excel ribbon

Commands can only be added to custom groups . So, before adding a command, be sure to create a custom group on an inbuilt or custom tab first, and then perform the below steps.

In the list under Customize the Ribbon , select the target custom group.
In the Choose commands from drop-down list on the left, select the list from which you want to add commands, for example, Popular Commands or Commands Not in the Ribbon .
In the list of commands on the left, click the command you want to add.
Click the Add button.
Click OK to save the changes.

Show icons instead of text labels on the ribbon

If you're using a small monitor or a laptop with a small screen, every inch of screen space matters. To save some room on the Excel ribbon, you can remove text labels from your custom commands to show only icons. Here's how:

In the right part of the Customize the Ribbon window, right-click on a target custom group and select Hide Command Labels from the context menu.

Hide text labels and show only icons on the ribbon.

You can only hide text labels for all the commands in a given custom group, not just for some of them.
You cannot hide text labels in built-in commands.

Rename ribbon tabs, groups and commands

In addition to giving your own names to custom tabs and groups that you create, Excel allows you to rename the built-in tabs and groups. However, you cannot change names of the inbuilt commands, only commands added to custom groups can be renamed.

To rename a tab, group or custom command, carry out these steps:

On the right side of the Customize the Ribbon window, click on the item you want to rename.
Click the Rename button below the list if tabs.
In the Display name box, type the name you want, and click OK .
Click OK to close the Excel Options window and view your changes.

Rename a ribbon group and choose an icon for it.

Note. You can change the name of any custom and build-in tab, except the File tab that cannot be renamed.

Move tabs, groups and commands on the ribbon

To know exactly where everything is located on your Excel ribbon, you can put tabs and groups in the most convenient places. However, the build-in commands cannot be moved, you can only change order of commands in custom groups.

To rearrange items on the ribbon, this is what you need to do:

In the list under Customize the Ribbon , click on the tab, group, or command in a custom group that you wish to move.
Click the Up or Down arrow to move the selected item left or right on the ribbon, respectively.
When the desired order is set, click OK to save the changes.

Note. You can change the placement of any build-in tab such as Home , Insert , Formulas , Data , and others, except the File tab that cannot be moved.

Remove groups, custom tabs and commands

While you can remove both default and custom groups, only custom tabs and custom commands can be removed. The build-in tabs can be hidden ; built-in commands can nether be removed nor hidden.

To remove a group, a custom tab or command, do the following:

In the list under Customize the Ribbon , select the item to be removed.
Click the Remove button.

Remove a custom command from the ribbon.

Tip. It is not possible to remove a command from a built-in group. However, you can make a custom group with the commands you need, and then remove the entire built-in group.

Hide and show tabs on the ribbon

If you feel the ribbon contains a couple of extra tabs that you never use, you can easily hide them from view.

To hide a ribbon tab, simply uncheck its box in the list of tabs under Customize the Ribbon , and then click OK .
To show a ribbon tab, select the box next to it, and click OK .

Note. You can hide both custom and built-in tabs, except the File tab that cannot be hidden.

Customize contextual tabs on Excel ribbon

How to reset Excel ribbon to default settings

If you've made some ribbon customizations, and then want to revert to the original setup, you can reset the ribbon in the following way.

To reset the entire ribbon :

In the Customize the Ribbon window, click Reset , and then choose Reset all customizations .

To reset a specific tab :

When you choose to reset all tabs on the ribbon, this also reverts the Quick Access Toolbar to the default state.
You can only reset the built-in tabs to their default settings. When you reset the ribbon, all custom tabs are removed.

How to export and import a custom ribbon

If you invested quite a lot of time in customizing the ribbon, you may want to export your settings to another PC or share your ribbon customizations with someone else. It's also a good idea to save your current ribbon configuration before migrating to a new machine. To have it done, please follow these steps.

Import a custom ribbon:

The ribbon customization file that you export and import also includes the Quick Access Toolbar customizations.
When you import a customized ribbon to a specific PC, all prior ribbon customizations on that PC are lost. If you think you might want to restore your current customization at a later point, please be sure to export your current ribbon before importing any new customizations.

That's how you personalize the ribbon in Excel. I thank you for reading and hope to see you on our blog next week!

12 comments

Hi, I want to create a menu in the ribbon. It should look like this

My company Logos Icons

When clicking on Logos I want powerpoint to open another powerpoint containing a set of logos. Likewise for Icons choice.

How can I do this?

I am transferring to a new PC.

In the process I also migrate Word, Excel 2010, and I have imported my custom ribbons, (I lose too many of my useful functions by going to the latest Word & Excel!)

Problem is, the location of the my custom add-in files has also changed, Is there a way to globally change the path to this new location instead of reassigning and editing each individual command?

Advice is highly appreciated.

I am using Excel 2013. How do I move/copy a command that is currently showing in the quick access ribbon to the main ribbon? I have tried showing all commands when customizing the main ribbon but what I'm wanting to add isn't showing. Please help!

I have a small in house network of two computers, each running Office 2019. Computer #1 has an Excel workbook that is shared (legacy). Computer #2 has a shortcut on its desktop pointing to the workbook on Computer #1. On Computer #1 I customized the ribbon, adding a few tabs. Now, when Computer #2 opens the workbook on Computer #1 it does not see the customized ribbon's new tabs. In order to fix this, should I customize the ribbon on Computer #2, adding the new tabs? Is there another solution? Thanks very much.

I would like to add one custom Main Tab (Macro) in the customize ribbon just for a certain excel work book not for all the files.

when i did this it was applied for all of them !!

is there a way to apply this custom tab to a single excel workbook only??

Thanks in advance.

I am also facing the same issue, please letme know in case you have found any solution to that.

Any solution for this? It should be included in the guide.....

Hello Svetlana, have a nice day My question: can I add a preferred font and character dimension in the ribbon ( a new group with all my preferred additional function, so go faster) ? Thanks any case, also fore negative answer

I would like to add a new field to my ribbon that I can insert in a document by just clicking on some tab, the same way I add an Insert Page Break or an Insert Section Break Next Page.

I have the field code, and it works well, but I don't know how to insert it somewhere so I can just place my cursor where I want the field code and then click.

The field is to be inserted at the end of a section and will check to see if the last page of that section where it is inserted is an even- or odd-numbered page. If it's an even-numbered page, it does nothing. If it's an odd-numbered page, it creates a new, even-numbered page with a header and footer before inserting the next section. This will avoid the blank page without a header and footer that is inserted in that same condition if you use Insert Section Next Odd Page. Also, I can include a text on the inserted page if I want to.

So, how can I do that? Like I said, I have the field code, and just need to know where and how to put it into Word so I can easily insert the code where I want. Otherwise, I have to keep copying the code from another document and inserting it manually.

Thanks...Bill Smart

I would like to add to my customized ribbon the icons for some of the ablebits function such as merge or duplicate.... how can i do that in excel 2019?

It starts at the top with go to File Options But I do not have an options item in the File drop down menu, so not a lot of use to me. Is this just out of date?

In Excel 2010 and later versions, there is no File drop down menu. When you click the File tab on the ribbon, you'll jump into Excels' backstage view. The Options button is at the very bottom of the left pane.

Alternatively you can right-click anywhere on the ribbon and select "Customize the Ribbon…" from the context menu. This will open the same Excel Options dialog window.

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,921 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"

Step 2 Click on the Developer tab and click Button.

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

↑ 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

Watch Articles

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

wikiHow Tech Help:

Tech troubles got you down? We've got the tips you need

Power Spreadsheets

Excel and VBA tutorials and training. Learn how to use Microsoft Excel and Visual Basic for Applications now.

Custom Excel Ribbon In 7 Easy Steps: The Introductory Tutorial

[Some of the links in this Excel Tutorial are affiliate links, which means that if you choose to make a purchase, I will earn a commission. This commission comes at no additional cost to you.]

The Excel Ribbon is the main component of Excel's user interface .

If you do a quick online search, you'll notice that there are several forum threads where people ask for advice regarding the topic of customizing the Excel Ribbon with VBA.

Excel VBA Tutorial about how to customize the Ribbon

This is understandable. After all, there are several reasons why you may want to create a custom Excel Ribbon . In Mastering VBA for Microsoft Office 2016 , author Richard Mansfield provides the following examples :

Create dynamic Ribbon effects (for example, hiding, disabling or displaying particular items during run-time) based on particular criteria, such as the behavior of the user (for example, opening or closing a particular Excel workbook or an add-in ).
Hide features.
Add-step through wizards .
Create custom-designed Ribbons.
Automate the management of the Ribbon.

Here's the deal:

Usually, customizing the Excel Ribbon has been considered to be unintuitive and difficult. For starters, if you follow the usual approach to customizing the Ribbon, you'll realize that your VBA knowledge isn't enough, but you also need to learn and understand XML (which I introduce below).

My purpose with this Excel tutorial is to provide you with the tools and information that will help you start creating custom Excel Ribbons immediately in just 7 steps (plus an optional eighth step), without the need to become an advanced VBA developer or having to learn XML . I discuss the main approaches to customizing the Excel Ribbon, along with the main reasons why I chose the approach and tools that I cover in this tutorial, below.

The following table of contents lists the main sections within this Excel tutorial:

Table of Contents

Let's start by understanding…

Excel Ribbon, Tabs, Groups And Buttons

If you've been using Excel for enough time, you probably know what the Ribbon, Ribbon tabs, Groups of Commands and Buttons are. However, as I show below, you'll probably work a lot with these elements whenever customizing the Ribbon.

Therefore, in this section, I provide a few screenshots and brief explanations of what each of these objects looks like within the Excel interface. Let's start with…

The Excel Ribbon

The following screenshot shows an Excel Ribbon.

The Ribbon was introduced in Excel 2007. The Ribbon replaces the previously existing menus and toolbars. Overall, as explained by Excel guru John Walkenbach in the Excel 2016 Bible , the Ribbon is simply “a collection of icons at the top of the screen “.

The Ribbon, as mentioned in Excel 2016 Power Programming with VBA , is organized in a hierarchy where:

Tabs are across the top of the Ribbon.
Tabs consist of Groups of Commands.
Groups of Commands are composed of buttons and other controls.

The following sections show each of these items.

Ribbon Tabs

The Excel Ribbon groups commands in different tabs . Therefore, the commands that appear within the Ribbon change depending on which tab you've selected.

You can identify the different tabs using their labels , which appear above the icons of the Ribbon. The following screenshot shows an Excel Ribbon with 10 different tabs: (i) File, which takes you to the Backstage View, (ii) Home, (iii) Insert, (iv) Page Layout, (v) Formulas, (vi) Data, (vii) Review, (viii) View, (ix) RC, which is the Ribbon Commander tab, and (x) Developer.

The precise appearance of your Excel Ribbon may differ from the above. The following are some possible reasons for this:

Add-Ins or other programs may add tabs to the Excel Ribbon. This is the case, for example, with the RC (Ribbon Commander) tab that appears in the screenshot above.
Excel includes certain Contextual tabs. These tabs are displayed whenever needed. This is usually the case when you select certain objects (such as charts, shapes or tables) and Excel displays a Contextual tab that includes particular tools and features that you can use on that particular object.
As I explain below, you can customize the Ribbon by, among others, adding new tabs or Groups of Commands.

Groups Of Commands

As I anticipate in the previous section, what commands that you can see in the Ribbon depends on which tab you've selected.

Within each tab, commands are further organized (or grouped) in different Groups of Commands.

For example, the following image displays the Home tab and its 7 Groups of Commands: (i) Clipboard, (ii) Font, (iii) Alignment, (iv) Number, (v) Styles, (vi) Cells, and (vii) Editing.

Buttons And Other Controls

At its most basic level, the Ribbon is composed of different controls. You access or execute commands through buttons and other controls that appear in the Ribbon.

There are several different styles of controls . I explain several of the most important ones, and how you can start creating them, below.

For the moment, as an example, let's take a look at the Font Group of Commands within the Home tab of the Ribbon.

This particular Group of Commands contains the following controls:

Custom Excel Ribbon: The Basics

The most basic way in which you can customize the Excel Ribbon is by using the features within the Excel Options dialog.

Customize Ribbon tab in Excel Options dialog

The Excel Options dialog allows you to customize a few aspects of the Ribbon, including the following:

Adding new tabs and Groups of Commands.
Adding additional commands.
Hiding or unhiding tabs.
Renaming tabs.

This way of modifying the Ribbon isn't what this post focuses on . I may write about this particular topic in a future blog post. If you want to receive an email when I publish new content in Power Spreadsheets, please feel free to register for our Newsletter by entering your email address below:

This doesn't mean that I consider these basic modifications useless. In fact, I usually use the Excel Options dialog for purposes of customizing the Ribbon and Quick Access Toolbar. I appreciate how easy and simple is to customize the Ribbon using the Excel Options dialog box.

However, if you rely solely on the Excel Options dialog box when customizing the Ribbon, you'll be subject to a few limitations.

In other words, modifying the Ribbon through the Excel Options dialog is easy and simple. It isn't particularly flexible nor powerful . As stated in Mastering VBA for Microsoft Office 2016 :

VBA programmers may want to customize the Office applications' Ribbons programmatically (via macro code as opposed to the user manually manipulating the Options dialog box).

If you're searching for ways to modify the Ribbon with code, you may find that most resources make reference to RibbonX. I provide a few examples of useful resources and add-ins about this particular topic further below.

Following a similar line of thought, in Excel 2016 Power Programming with VBA , authors Dick Kusleika and Mike Alexander state that:

You can’t perform any Ribbon modifications using VBA. Rather, you must write RibbonX code and insert the code into the workbook file — outside of Excel.

This post doesn't focus on RibbonX either. As explained in Excel 2016 Power Programming with VBA :

The topic is complex enough to be the subject of an entire book.

For purposes of this tutorial, you probably just need to know that RibbonX :

Is Extensible Markup Language (XML). I provide a brief introduction to XML below.
Describes the characteristics of the controls within the Ribbon. These characteristics include the location and appearance of the control.

I may cover the topic of RibbonX in more detail within future blog posts. If you want to be notified whenever I publish new content in Power Spreadsheets, please feel free to register for our Newsletter by entering your email address below:

Another relatively common (although even more complex) way of modifying the Ribbon involves writing Component Object Model (COM) add-ins.

This post doesn't cover COM add-ins either.

The main reason why I chose to focus on the Ribbon Commander Framework within this Excel tutorial (instead of RibbonX or COM add-ins) is that, in my opinion, the Ribbon Commander is simply more convenient . In particular, I consider the Ribbon Commander Framework to be significantly easier to understand and use than the more traditional approaches to Ribbon customization .

For example, in Excel VBA Programming for Dummies , Excel guru John Walkenbach states that:

You can make automatic changes to the Ribbon with Excel 2007 and later versions, but it's not a simple task.

Walkenbach also states that (i) users may realize that modifying the Ribbon isn't particularly intuitive and (ii) XML “is way too complex for the beginner VBA programmer”.

Along similar lines, in Excel 2016 VBA and Macros , experts Bill Jelen (Mr. Excel) and Tracy Syrstad describe the process as being “not that simple” but “not impossible”.

The resources and Excel authorities I cite above were making reference (mostly) to how to customize the Ribbon using XML. Therefore, the comments above don't apply to the Ribbon Commander Framework that I cover in this blog post. As I mention at the beginning of this blog post, I consider that the Ribbon Commander Framework (combined with this Excel tutorial) allows you to easily start customizing the Excel Ribbon without the need to become an advanced VBA developer .

Despite this tutorial not focusing on RibbonX, you may find that some basic knowledge of XML helps you to better understand the procedure and example for customizing the Excel Ribbon that I describe further below, therefore, let's take a look at:

Extensible Markup Language (XML): A Brief Introduction

In Mastering VBA for Microsoft Office 2016 , Author Richard Mansfield provides a good basic description of XML :

It's a way to surround data with descriptions of that data.

XML (as implied by its name) is a markup language. As explained in this thread at Stack Exchange , you can use markup languages “to control the presentation of data” . In other words, XML doesn't really do anything. It provides formatting or description information.

As a consequence of the above, statements that use XML usually have a structure such as the following:

#1: A descriptive tag.
#2: The data being described.
#3: A closing tag.

In other words, when you use XML, data is usually enclosed by an opening and a closing tag.

However, as explained by experts Dick Kusleika and Michael Alexander in Excel 2016 Power Programming with VBA , XML sometimes uses self-closing tags . In this case, the structure of a statement is slightly different from the one I mention above.

Further to the above, the elements within XML are organized in a hierarchy . In other words, similar to what happens in the Excel VBA object model, each XML element can contain further (child) elements.

I provide further details of the Ribbon Commander object model in a section below.

For the moment, let's start to take a look at what you need to do to customize the Excel Ribbon. For these purposes, you must…

Download, Install And Activate The Ribbon Commander Before Beginning

As I mention in the previous sections, the method of customizing the Excel Ribbon that I explain in this blog post relies on the Ribbon Commander Framework. The Ribbon Commander is a framework for rapid Office user interface development .

The main purpose of the Ribbon Commander is to both simplify and extend the prevalent XML-based programming model for the Office user interface .

Therefore, for purposes of following through with this tutorial, and customizing the Excel Ribbon in the 7 (plus 1 optional) steps that I describe below, you must be able to access and use the Ribbon Commander framework. To achieve this, simply do the following:

Step #1: Download The Ribbon Commander

You can download the latest version of the Ribbon Commander by clicking here .

Get a Free Trial of the Ribbon Commander

The Ribbon Commander comes with a 30-day free trial without restrictions to the framework functionality . You can also purchase a 1-year subscription by clicking here .

Step #2: Install The Ribbon Commander

You can find the instructions to install the Ribbon Commander here .

Step #3: Activate The Ribbon Commander

If necessary, please refer to the activation instructions here .

Once you have downloaded, installed and activated the Ribbon Commander, you're ready to continue with this tutorial and follow the example. Let's start the journey into the Ribbon Commander Framework.

Introduction To The Ribbon Commander Object Model

For this particular blog post focusing on the Excel Ribbon, I (mostly) make reference to the 2 following resources :

Resource #1: The Ribbon Commander Documentation .
Resource #2: The Custom UI XML Markup Version 2 Specification [MS-CUSTOMUI2] at the Microsoft Dev Center .

Some of the concepts that I introduce in other blog posts (such as The Essential Guide to Excel's VBA Object Model And Object References , VBA Object Properties: An Excel Tutorial or Object Methods In Excel VBA: Fundamentals Training ) are roughly applicable and may help you understand the Ribbon Commander Framework . In my opinion, this is one of the main advantages of customizing the Excel Ribbon with the Ribbon Commander Framework.

Despite the above, the differences between the Ribbon Commander Framework and some of the other topics I cover in Power Spreadsheets are substantial enough to justify spending some time going through the main elements and the basic structure of the Ribbon Commander Framework's object model.

The object model is an object hierarchy of all the objects you can use . An object hierarchy has several levels:

Level #1: At the top of the hierarchy, you have a single element. This may be referred to as the root.
Level #2: The root contains some child elements or contained controls.
Level #3: The elements in level #2 may contain further child elements or controls.
Level ##: Elements may contain other controls. If you continue going down the hierarchy long enough, you eventually reach a point where elements don't have any children.

To a certain extent, this way of organizing the different elements can help you understand what's going on within the Ribbon Commander Framework. More precisely, as explained in Mastering VBA for Microsoft Office 2016 :

In XML, the order of the elements matters. XML is hierarchical (also known as nested or a tree), meaning that you need to put elements inside each other in the proper order.

In order to understand the Ribbon Commander Framework, let's start by taking a look at the root element, which sits at the top of the model hierarchy: rxCustomUI. This object models the CT_CustomUI element of the Custom UI XML Markup Version 2 Specification.

In other words, rxCustomUI is the root element that serves as the parent of all other elements within the custom user interface hierarchy.

As a consequence of the above, you'll see rxCustomUI being used constantly throughout this blog post.

rxCustomUI contains 6 controls :

Control #1: rxBackstage. rxBackstage models the CT_Backstage element of the Custom UI XML Markup Version 2 Specification. This element specifies a reference to the Backstage .
Control #2: rxCommands. rxCommands models the CT_Commands element of the Custom UI XML Markup Version 2 Specification. This element specifies a list of commands .
Control #3: rxContextMenus. rxContextMenus models the CT_ContextMenus element of the Custom UI XML Markup Version 2 Specification. It specifies a list of built-in context menu controls .
Control #4: namespaces. Namespaces help you avoid conflicts of element names . Within the Ribbon Commander Framework, the namespaces element contains a collection of XML namespaces that can be used within the control's context.
Control #5: rxRibbon. rxRibbon models the CT_Ribbon element of the Custom UI XML Markup Version 2 Specification. CT_Ribbon sets a reference to the main Ribbon of the relevant application. In this particular case, the specified reference is to Excel's Ribbon.
Control #6: ribbonXControls. ribbonXControls makes reference to the collection of all Dynamic RibbonX controls contained within the relevant context.

Each of these controls, in turn, is capable of containing other controls. For example, rxRibbon (control #5 above) can contain the following 5 controls :

Control #1: rxContextualTabs. rxContextualTabs models the CT_ContextualTabs element of the Custom UI XML Markup Version 2 Specification. This element specifies a list of contextual tab sets .
Control #2: rxOfficeMenu. rxOfficeMenu models the CT_officeMenu element of the Custom UI XML Markup Version 2 Specification. It specifies (and you use it to reference) the built-in Office Menu in Office 2007 .
Control #3: rxQat. rxQat models the CT_Qat element of the Custom UI XML Markup Version 2 Specification. This element specifies the Quick Access Toolbar .
Control #4: ribbonXControls. As I explain above, ribbonXControls makes reference to the collection of contained RibbonX controls.
Control #5: tabs. tabs models the CT_Tabs element of the Custom UI XML Markup Version 2 Specification. It specifies a collection of tab controls .

The process repeats itself. Each of these 5 controls can contain other controls. For example, a tab control (rxTab) contains the following 2 controls :

Control #1: groups. groups is a collection of Groups of Commands within a Ribbon tab.
Control #2: ribbonXControls. ribbonXControls, as in the previous levels explained above, refers to the collection of contained RibbonX controls.

To finish the exploration of the section of the Ribbon Commander object model that is most relevant for purposes of this Excel tutorial, let's take a look at the 17 controls contained within a group (rxGroup) control :

Control #1: boxes. boxes is a collection of rxBox controls. rxBox models the CT_Box element of the Custom UI XML Markup Version 2 Specification. More precisely, rxBox is a grouping container . The child controls of this container are then aligned horizontally or vertically.
Control #2: buttonGroups. buttonGroups is a collection of rxButtonGroup controls. rxButtonGroup models the CT_ButtonGroup element of the Custom UI XML Markup Version 2 Specification. rxButtonGroup is also a grouping container . Child controls of rxButtonGroup are visually grouped together.
Control #3: buttons. buttons is a collection of rxButton controls. rxButton models the CT_Button element within the Custom UI XML Markup Version 2 Specification. You can use rxButton to specify a standard push button . In other words, these are what I describe above as simple buttons.
Control #4: checkBoxes. checkBoxes is a collection of rxCheckBox controls. rxCheckBox models the element CT_CheckBox of the Custom UI XML Markup Version 2 Specification. You can use rxCheckBox to specify a standard check box control.
Control #5: comboBoxes. comboBoxes is a collection of rxComboBox controls. rxComboBox models the CT_ComboBox element of the Custom UI XML Markup Version 2 Specification. You can use rxComboBox to specify a standard combo box control.
Control #6: controls. controls is a collection of rxControlClone controls. rxControlClone models the element CT_ControlClone of the Custom UI XML Markup Version 2 Specification. You can use the controls of this collection to enable, disable or clone built-in controls .
Control #7: dialogLauncher. dialogLauncher is a collection of rxDialogLauncher controls. rxDialogLauncher models the CT_DialogLauncher element of the Custom UI XML Markup Version 2 Specification. rxDialogLauncher allows you to specify a button that opens an additional UI . I show an example of a built-in dialog box launcher above.
Control #8: dropDowns. dropDowns is a collection of rxDropDownRegular controls. rxDropDownRegular models the element CT_DropDownRegular within the Custom UI XML Markup Version 2 Specification. You can use rxDropDown to specify a standard drop-down control.
Control #9: dynamicMenus. dynamicMenus is a collection of rxDynamicMenu controls. rxDynamicMenu models the CT_DynamicMenu element of the Custom UI XML Markup Version 2 Specification. rxDynamicMenu allows you to specify a particular menu control where the contents are dynamically populated at run time.
Control #10: editBoxes. editBoxes is a collection of rxEditBox controls. rxEditBox models the element CT_EditBox within the Custom UI XML Markup Version 2 Specification. You can use rxEditBox to specify a standard edit box control.
Control #11: galleries. galleries is a collection of rxGallery controls. rxGallery models the CT_Gallery element of the Custom UI XML Markup Version 2 Specification. rxGallery allows you to specify “a gallery control that displays a drop-down grid of selection items”.
Control #12: labelControls. labelControls is a collection of rxLabelControl controls. rxLabelControl models the element CT_LabelControl within the Custom UI XML Markup Version 2 Specification. You can use rxLabelControl to specify a control displaying a string .
Control #13: menus. menus is a collection of rxMenu controls. rxMenu models the CT_Menu element of the Custom UI XML Markup Version 2 Specification. rxMenu allows you to specify a standard drop-down menu control.
Control #14: ribbonXControls. ribbonXControls is the collection of all contained RibbonX controls.
Control #15: separators. separators is a collection of rxSeparator controls. rxSeparator models the element CT_Separator within the Custom UI XML Markup Version 2 Specification. You can use rxSeparator to specify a vertical line separator control.
Control #16: splitButtons. splitButtons is a collection of rxSplitButton controls. rxSplitButton models the CT_Splitbutton element of the Custom UI XML Markup Version 2 Specification. rxSplitButton allows you to specify a split button . Split buttons, as I explain above, contain (i) a primary (or simple) button, and (ii) a drop-down menu.
Control #17: toggleButtons. toggleButtons is a collection of rxToggleButton controls. rxToggleButton models the element CT_ToggleButton within the Custom UI XML Markup Version 2 Specification. You use rxToggleButton to specify a toggle button control . I provide some examples of built-in toggle buttons within Excel above.

Notice how several of these controls match the 5 types of commands that appear within the Font Group of Commands within the Home Tab of the Ribbon.

Going into the details (such as properties and delegates) of all of these controls exceeds the scope of this tutorial. I may, however, cover them in future blog posts. If you want to receive an email whenever I publish new content in Power Spreadsheets, please register for our Newsletter by entering your email address below:

Graphically, the portion of the Ribbon Commander Framework object model that I describe above looks roughly as follows:

Object Model Hierarchy for the Ribbon Commander Framework

The image above shows a limited portion of the Ribbon Commander Framework. Based on this, it may seem that identifying the elements you need for purposes of working with the Ribbon Commander Framework is a complicated task.

Fortunately, this isn't really the case. As explained by author Richard Mansfield in Mastering VBA for Microsoft Office 2016 , when you're working with the Ribbon, the usual order of elements you need to understand is as follows :

Element #1: Ribbon.
Element #2: Tabs.
Element #3: Tab.
Element #4: Group.
Element #5: Button, or other relevant control.

In fact, below, you can see how I use precisely this hierarchy when customizing the Excel Ribbon with the Ribbon Commander Framework . Additionally, notice that the list of elements suggested by Mansfield precisely matches the main Ribbon elements that I introduce at the beginning of this tutorial.

Now that you have a good idea of how the object model of the Ribbon Commander Framework works, and the main objects you'll be working with, let's take a look at…

Properties, Methods, Delegates And Events In The Ribbon Commander Framework

In addition to containing other elements (as I explain in the previous section), each of the elements within the Ribbon Commander Framework has properties, methods, delegates and events .

Properties are, generally, the attributes of an object. In other words, they describe the objects.
Methods are, from a general perspective, the actions or operations that you can perform with a particular object. A special type of method you'll be working with in the Ribbon Commander Framework are static methods. Generally, you can use static methods even if you haven't initialized the relevant class . In other words, you can invoke a static method even if an instance of the relevant class doesn't exist yet.
Delegates are, from a broad perspective, pointers . To understand delegation, it may be helpful if you think in terms of sender and receiver. Usually, when the sender receives a request, it points to the receiver. The receiver is generally the element that actually does the work.
Events are, as implied by their name, simply something (an action) that happens .

Don't worry if things aren't absolutely clear at the moment. In the sections below, I explain some of the most important properties, methods, delegates and events you need to understand when working with the Ribbon Commander Framework.

To do this, let's take a look at a practical example of a program you can use to customize the Ribbon by using the Ribbon Commander Framework.

Create A Custom Excel Ribbon Using VBA: Example

For purposes of this tutorial, I'll show you how to create a custom Excel Ribbon using the Ribbon Commander Framework in 7 (plus 1 optional) easy steps. The resulting sample Sub procedure is called “Custom_Ribbon”.

This Excel VBA Custom Ribbon Tutorial is accompanied by an Excel workbook containing the macros I use (including the “Custom_Ribbon” Sub procedure). You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter .

Step #1: Create A Reference To rxCustomUI

As I explain above, the object at the top of the Ribbon Commander Framework model is rxCustomUI.

Therefore, rxCustomUI is the parent of all the other elements you'll be working with throughout this example.

For purposes of getting the reference to the relevant rxCustomUI instance, you can use either of the following 2 static methods of rxCustomUI:

Static Method #1: create. create is the factory method you use for purposes of working with rxCustomUI objects. The consequence of using create depends on whether the relevant context already exists or not: If the context doesn't exist, the create method creates a new context. If the context already exists, the rxCustomUI object created by the create method joins the previously existing context.
Static Method #2: defaultInstance. defaultInstance, as implied by its name, makes reference to the default rxCustomUI object within the current session.

For this particular example, I'll use defaultInstance . defaultInstance doesn't have any parameters.

Therefore, I start the Custom_Ribbon procedure by inserting the following 2 statements:

Dim myCustomUI As rxCustomUI Set myCustomUI = rxCustomUI.defaultInstance

Code of Step #1 to create custom Excel Ribbon

Let's take a closer look at the 2 lines of code above to understand what they do:

Line #1: Dim myCustomUI as rxCustomUI

This line of code uses the Dim statement to declare the myCustomUI object variable . myCustomUI is declared as of the rxCustomUI object type.

Line #2: Set myCustomUI = rxCustomUI.defaultInstance

You can use the Set statement to assign an object to an object variable . In this particular case:

The assigned object is rxCustomUI.defaultInstance (the default rxCustomUI object).
The object variable to which the object is assigned is myCustomUI.

Despite the above, whenever you're creating production code, the recommendation is to rely on the create method for purposes of avoiding potential conflicts with other applications . Therefore, let's take a quick look at the main parameters of…

The Create Method

The create method has the following 4 parameters :

Parameter #1: contextID. contextID is a string that identifies the context . It's default value is null.
Parameter #2: description. As implied by its name, description is a string that describes the context . It's default value is “Ribbon Commander”. The description parameter only has an effect when you're creating a new context. In other words, if the rxCustomUI object created by the create method joins a previously existing context, description doesn't have an effect.
Parameter #3: dispatchScope. dispatchScope allows you to determine the dispatch mode of the rxCustomUI context . The dispatch scope is what determines which parts of a program can access or refer to the rxCustomUI context . The dispatchScope parameter can take either of the following 2 rxDispatchScope values : Value #1: DispatchScope_local which, as implied by its name, results in local (VBA class) dispatch. DispatchScope_local is the default value of the dispatchScope parameter. Value #2: DispatchScope_global which, as you may imagine, results in global (VBA standard module) dispatch. One of the main differences between using DispatchScope_local and DispatchScope_global is the lifetime of the relevant context. If you use DispatchScope_local, the lifetime of the context is tied to the lifetime of the rxCustomUIs within the context. As explained within the Ribbon Commander documentation, “when the last rxCustomUI of the context is destroyed, the context is also destroyed”. On the other hand, when you use DispatchScope_global, the relevant rxCustomUI is added to the globalCustomUIs collection. This collection includes all the rxCustomUIs within the current session that have a global dispatch. This includes, among others, the default rxCustomUI object (defaultInstance) to which I make reference above. This particular member of the collection is read-only. As a consequence, you can't remove the default rxCustomUI object from the globalCustomUIs collection. Once an rxCustomUI is added to the globalCustomUIs collection, you must explicitly remove it from the collection for purposes of destroying it.
Parameter #4: customUIMode. You can use customUIMode to specify the state or mode of the relevant rxCustomUI context . The customUIMode parameter can take either of the following 2 rxCustomUIMode values : Value #1: CustomUIMode_static, which refers to static mode. Value #2: CustomUIMode_dynamic, which refers to dynamic mode. CustomUIMode_dynamic is the default parameter value. Since it generally allows for smoother rendering, it's also the suggested choice.

In a previous section, I quote technology author Richard Mansfield, who states that the usual order of elements you work with within the Excel Ribbon is as follows:

Element #2: Tabs

In the following steps, I show how you can start working with each of these elements when using the Ribbon Commander framework to create a custom Excel Ribbon. Let's start with element #1:

Step #2: Create A Reference To The Ribbon

As explained within this example within the Ribbon Commander documentation:

Each rxCustomUI object owns a unique rxRibbon object.

As I explain above, rxRibbon:

Is one of the controls contained within rxCustomUI.
Models the CT_Ribbon element of the Custom UI XML Markup Version 2 Specification.
Creates a reference to the main Ribbon of the relevant application.

To create a reference to the Ribbon , I use the following 2 statements. Notice that they're materially similar to those that I use above when creating a reference to rxCustomUI.

Dim myRibbon As rxRibbon Set myRibbon = myCustomUI.ribbon

At this point, the sample Custom_Ribbon Sub procedure looks as follows:

Code for Step #2 to create custom Excel Ribbon

The 2 new lines of code proceed in a substantially similar way as that I explain in the previous step #1. In other words:

Line #1 (Dim myRibbon As rxRibbon) uses the Dim statement to declare the myRibbon object variable. myRibbon is declared as of the rxRibbon object type.
Line #2 (Set myRibbon = myCustomUI.ribbon) uses the Set statement to (i) assign the main Ribbon within the rxCustomUI instance (myCustomUI.ribbon) to (ii) the myRibbon object variable.

Once the Sub procedure has the reference to the Ribbon, we can proceed to the next element we need to work with:

Step #3: Create A New Tab

When I introduce the object model of the Ribbon Commander Framework above, I list tabs as one of the controls contained within rxRibbon. As I explain there, tabs:

Models the CT_Tabs element of the Custom UI XML Markup Version 2 Specification.
Specifies a collection of tab controls.

However, most of the time you work with a particular object from a collection.

This is the rule that applies in this case. Therefore, to insert a new Ribbon tab, you use rxTab . rxTab:

Models the CT_Tab element of the Custom UI XML Markup Version 2 Specification.
Specifies a Ribbon tab control.

rxTab has 15 properties . Several of them model the equivalent attributes of the CT_Tab element within the Custom UI XML Markup Version 2 Specification.

Within the sample Custom_Ribbon Sub procedure that I show within this tutorial, I only use 2 properties. These are keytip and label, which I explain below.

However, since you may need to work with other properties of rxTab, let's take a look at them. For these purposes, I group the 15 properties of rxTab in the following 4 groups:

Group #1: ID Properties (3 Properties)

You can use the following 3 properties to specify identifiers of different elements or controls:

Property #1: id. id makes reference to the identifier of the custom UI element . id models a ST_UniqueID attribute of the Custom UI XML Markup Version 2 Specification.
Property #2: idMso. idMso refers to the identifier of a built-in control . idMso models an ST_ID attribute of the Custom UI XML Markup Version 2 Specification.
Property #3: idQ. idQ is a qualified control identifier . Qualified control IDs can be useful, as they allow different add-ins to modify the same custom object (such as a group or tab). idQ models a ST_QID attribute of the Custom UI XML Markup Version 2 Specification. You can generally use idQ to refer to controls or elements that are created by other custom UI documents.

As a general rule:

Identifiers should be unique . Therefore, all custom UI elements must have a unique ID.
id, idMso and idQ are mutually exclusive . Therefore, you shouldn't use them together. Specify a single one of the properties on your custom controls. In general, as explained in Mastering VBA for Microsoft Office 2016 , “Mso” stands for Microsoft Office and, when appended to a particular property, it makes reference to built-in items. Similarly, “Q” stands for qualified . Plain properties that don't use the Mso or Q suffixes (such as id above), are generally references to custom elements.

Group #2: Insertion Location Properties (4 Properties)

You can use the following 4 properties for purposes of specifying the location where the new tab is to be inserted :

Property #1: insertAfterMso. insertAfterMso allows you to specify the built-in control after which the control you're referring to is inserted. insertAfterMso works with built-in controls. Therefore, you work with idMso identifiers, as I explain in the previous section.
Property #2: insertBeforeMso. insertBeforeMso is the opposite of insertAfterMso . In other words, you can use insertBeforeMso to specify the built-in control before which the control you're working with is inserted.
Property #3: insertAfterQ. insertAfterQ is similar to insertAfterMso . Therefore, you can use it to specify the control after which the control you're referring to is inserted. The main difference is that insertAfterMso works with built-in controls, which insertAfterQ works with qualified controls . Therefore, when working with insertAfterQ, you use idQ identifiers, as I explain in the previous section.
Property #4: insertBeforeQ. insertBeforeQ is the opposite of insertAfterQ . Therefore, you use insertBeforeQ to specify the qualified identifier of the control before which the control you're working with is inserted.
insertAfterMso, insertAfterQ, insertBeforeMso and insertBeforeQ are mutually exclusive . Therefore, you shouldn't use these properties together.
If you don't specify any of these properties: (i) the relevant control is appended to the existing controls and (ii) the order in which controls are organized is that set forth in the applicable XML.

Group #3: Read-Only Properties (2 Properties)

The following 2 properties are read-only . Therefore, you can't modify them.

Property #1: isEmpty. isEmpty allows you to check if the relevant control is empty or not. If the control is empty, isEmpty returns the Boolean value rxTrue.
Property #2: isLive. isLive can be used to check if the relevant control is live or not. If the control is live, isLive returns rxTrue.

Group #4: Other Properties (6 Properties)

The following are the remaining 6 properties of rxTab:

Property #1: keytip. keytip allows you to specify a particular string that acts as the KeyTip for a control . If you don't specify a keytip string, no KeyTip is displayed. keytip models a ST_Keytip attribute of the Custom UI XML Markup Version 2 Specification.
Property #2: label. label allows you to specify a string to be displayed as the control label . If you don't specify the label property, no label is displayed.
Property #3: parent. parent makes reference to the parent object of the control you're working with.
Property #4: tag. tag is custom data.
Property #5: visible. visible is a Boolean property (rxTrue or rxFalse) that allows you to specify whether the control you're working with is visible or not. The default value is to make the control visible.
Property #6: xml. xml, as explained within the Ribbon Commander documentation, “returns the xml node that corresponds to the state of the control.”

Now that you have a good idea of the different properties of rxTab, let's go back to the sample Custom_Ribbon Sub procedure and see how we can create a new tab :

For these purposes, I include the following statements in the sample macro:

Dim myTab As rxTab Set myTab = New rxTab With myTab .keytip = “Y2” .Label = “Power Spreadsheets” End With myRibbon.tabs.Add myTab

Let's take a closer look at each of the new lines of code:

Code for Step #3 to create custom Excel Ribbon

Line #1: Dim myTab As rxTab

As in the previous 2 steps (creating references to the rxCustomUI and the Ribbon), I start by using the Dim statement to declare the myTab object variable . I declare myTab as an rxTab object.

Line #2: Set myTab = New rxTab

Once again, as in the previous 2 steps, I use the Set statement to assign an object to an object variable . In this case:

The assigned object is a new rxTab (New rxTab). For these purposes, I use the New keyword.
The object variable to which the object is assigned is myTab.

Lines #3 And #6: With myTab | End With

These 2 lines of code are the opening and closing lines of a With… End With block.

The main purpose of a With… End With block is to simplify the syntax of the macro. This is achieved by executing several statements on a single object.

In the example we're looking at, all of the statements within the With… End With block (lines #4 and #5 below) are executed on the object represented by the myTab object variable.

Line #4: .keytip = “Y2”

This statement sets the keytip property of myTab to be equal to the string “Y2”. In other words, this line of code specifies that the KeyTip of the newly created tab is “Y2”.

As a consequence of the above, the resulting keyboard shortcut is “Alt + Y2”.

In case you're curious, the reason why I chose “Y2” as keytip is because the keytip for the Ribbon Commander is “Y1”.

When you're creating your own custom Excel Ribbon, you can set a different keytip property value, or avoid specifying one. If you choose to set up a KeyTip for your new tab, bear in mind any other previously existing KeyTips for purposes of avoiding conflicts.

Line #5: .Label = “Power Spreadsheets”

This line of code sets the label property of myTab. I set the label to be equal to the string “Power Spreadsheets”.

Line #7: myRibbon.tabs.Add myTab

As I mention above, tabs specifies a collection of tab controls.

In the previous lines of code within this step #3, I make reference to myRibbon. myRibbon is an object variable holding an rxTab. In other words, I use rxTab for purposes of specifying an individual tab control.

This line of code #7 makes reference to the tabs RibbonX collection. More precisely, it uses the add method to add an item (myTab) to the collection .

Now that the custom Ribbon has a new tab, we can move on to the next element we need to work with:

Step #4: Create A New Group Of Commands

rxTab is the parent of 2 controls, which are contained within it:

groups , which is a collection of groups.
ribbonXControls , which is a collection that includes all contained ribbonX controls.

In other words, any rxTab has a collection of rxGroup objects.

rxGroup models the CT_Group element of the Custom UI XML Markup Version 2 Specification. rxGroup “specifies a grouping of controls” within a tab.

rxGroup has 21 different properties . However, 15 of these properties mirror properties of rxTab, which I explain above. Therefore, let's start by pointing out which are these properties, whose basic characteristics I introduce in the previous section.

Property #1: id. You can use id to specify the identifier of a custom control.
Property #2: idMso. idMso allows you to specify the identifier of a built-in control.
Property #3: idQ. idQ specifies the qualified identifier of a control.
Properties #4 And #5: insertAfterMso and insertBeforeMso. insertAfterMso and insertBeforeMso allow you to specify the built-in control after (in the case of insertAfterMso) or before (with insertBeforeMso) which the control you're working with is inserted.
Properties #6 And #7: insertAfterQ and insertBeforeQ. insertAfterQ and insertBeforeQ work in a similar way to insertAfterMso and insertBeforeMso. The main difference between insertAfterQ (vs. insertAfterMso) and insertBeforeQ (vs. insertBeforeMso) is that insertAfterQ and insertBeforeQ work with qualified identifiers of controls, whereas insertAfterMso and insertBeforeMso work with identifiers of built-in controls.
Properties #8 And #9: isEmpty and isLive. isEmpty and isLive are read-only properties (you can't modify them) that you can use for purposes of checking (i) whether the control is empty (with isEmpty) or (ii) where the control is live (with isLive). These properties return the Boolean values rxTrue and rxFalse.
Property #10: keytip. keytip allows you to set a KeyTip for the control.
Property #11: label. You can use label to specify the label of the control.
Property #12: parent. parent is the parent of the control.
Property #13: tag. tag is custom data.
Property #14: visible. You can use visible to determine whether the control is visible or not.
Property #15: xml. xml returns the xml node corresponding to the control's state.

As mentioned above, I explain all of these properties more thoroughly in the relevant section above.

Considering the above, there are only 6 properties of rxGroup that I don't explain in previous sections of this blog post. Let's take a closer look at them:

Property #1: autoScale. You can use autoScale to determine whether the contents within the relevant control resize automatically as the size of the window in which they're displayed changes. autoScale is a Boolean property. Its default value is rxFalse. Therefore, if you omit the autoScale property, the contents of the Group of Commands aren't automatically resized.
Property #2: centerVertically. centerVertically allows you to specify whether the contents of the control are centered vertically within the relevant container. centerVertically is also a Boolean property. Its default value is also rxFalse. As a consequence, whenever you omit the centerVertically property, the contents of the Group of Commands are aligned at the top of the applicable container (instead of being centered vertically).
Property #3: image. image which is the property you use to specify a custom image or icon for the control. image models an ST_Uri attribute of the Custom UI XML Markup Version 2 Specification.
Property #4: imageMso. You can use imageMso to specify a built-in image or icon for the control. imageMso models an ST_ID attribute of the Custom UI XML Markup Version 2 Specification. imageMso is substantially similar to the image property. Their main difference is that image works with a custom image or icon, whereas imageMso works with a built-in image. image and imageMso are mutually exclusive . Therefore, you should use one or the other. If you use neither, no icon is displayed.
Property #5: screentip. screentip allows you to specify a string for a ToolTip (a small window with descriptive text displayed on mouse hover) for the control. If you don't use the screentip property, no ToolTip is displayed.
Property #6: supertip. You use supertip to specify a string for an Enhanced ScreenTip (larger than regular ToolTip) . If you don't specify the supertip property, no Enhanced ScreenTip is displayed.

Now that you're aware of the different properties of rxTab, let's take a look at how you can use this information to include a new Group of Commands within the new tab created in step #3 above for the custom Ribbon:

For these purposes, I add the following statements:

Dim myCommandGroup As rxGroup Set myCommandGroup = New rxGroup With myCommandGroup .autoScale = rxTrue .CenterVertically = rxFalse .Label = “Example Macros” End With myTab.groups.Add myCommandGroup

Notice that the structure of the newly added lines of code is substantially similar to the code added in step #3 above (when I created a new tab):

Code for Step #4 to create custom Excel Ribbon

Let's take a look at new lines of code:

Line #1: Dim myCommandGroup As rxGroup

As in the other similar statements explained previously, this line of code uses the Dim statement to declare the myCommandGroup object variable . myCommandGroup is declared as of the rxGroup object type.

Line #2: Set myCommandGroup = New rxGroup

Following other similar statements, this line of code uses the Set statement to assign an object to an object variable . More precisely, this statement (i) assigns a new rxGroup (New rxGroup) to (ii ) the myCommandGroup object variable.

Lines #3 & #7: With myCommandGroup | End With

These statements are exactly the same as those in lines #3 and #6 of the previous step #3, where I created a new tab. They are the opening and closing lines of a With… End With block.

As a consequence of using the With… End With construct, all of the statements within the block (lines #4, #5 and #6 below) are executed on the object represented by the myCommandGroup object variable.

Line #4: .autoScale = rxTrue

This statement sets the autoScale property of myCommandGroup to rxTrue.

As a consequence, the contents within myCommandGroup automatically resize when the size of the containing window changes.

Line #5: .CenterVertically = rxFalse

This line of code sets the CenterVertically property of myCommandGroup to rxFalse.

The consequence of this is that the contents of myCommandGroup aren't centered vertically . Rather, they're aligned at the top of the container.

Line #6: .Label = “Example Macros”

This is the equivalent of line #5 in the previous step #3 (adding a new tab).

The statement sets the label property of myCommandGroup to be “Example Macros”.

Line #8: myTab.groups.Add myCommandGroup

This line mimics line #7 of the previous step #3 (when I added a new tab to the Ribbon).

This line #8 uses the add method to add myCommandGroup to the groups collection .

Now that we have added a Group of Commands (myCommandGroup) to the newly created Ribbon tab (myTab), we can move to the last element we'll be working with (following the hierarchy suggested by author Richard Mansfield above).

Step #5: Create New Buttons Or Controls

When introducing the Ribbon Commander object model above, I list and explain the 17 different controls that are contained within rxGroup :

buttonGroups.
checkBoxes.
comboBoxes.
dialogLauncher.
dynamicMenus.
labelControls.
ribbonXControls.
separators.
splitButtons.
toggleButtons.

For this particular tutorial, I focus solely on one of the most basic control: rxButton .

Some of the controls I list above contain themselves the button control. In other words, what I explain below may also help you when working with controls such as rxBox, rxDialogLauncher and rxSplitButton.

Let's take a closer look at rxButton:

rxButton: The Standard Ribbon Button Control

As I explain above, rxButton:

Models the CT_Button element of the Custom UI XML Markup Version 2 Specification.
Allows you to specify a standard (push) button control.

rxButton has 24 different properties . Several of these properties are the rough equivalents of other object properties that I cover in previous sections of this tutorial.

Therefore, let's start by taking a look at the properties whose basic characteristics I introduce when explaining rxTab and rxGroup.

I explain the following 15 properties in more detail when covering rxTab above:

Property #1: id, is an identifier of a custom UI element.
Property #2: idMso, makes reference to the identifier of a built-in control.
Property #3: idQ, refers to a qualified control identifier.
Properties #4 And #5: insertAfterMso and insertBeforeMso allow you to specify the built-in control after (in the case of insertAfterMso) or before (when using InsertBeforeMso) which a particular control should be inserted.
Properties #6 And #7: insertAfterQ and insertBeforeQ allow you to specify the qualified control after (when using insertAfterQ) or before (in the case of InsertBeforeQ) which a certain control is inserted. The main difference between the 2 sub-groups properties above is that one works with built-in controls (insertAfterMso and insertBeforeMso), whereas the other works with qualified controls (insertAfterQ and insertBeforeQ).
Property #8: isEmpty allows you to check if a control is empty.
Property #9: isLive allows you to check if a control is live.
Property #10: keytip allows you to set the control's KeyTip.
Property #11: label allows you specify the control label.
Property #12: parent is the parent of the control.
Property #13: tag is custom data.
Property #14: visible determines whether the control is visible.
Property #15: xml returns the xml node corresponding to the control's state.

Similarly, I provide more details about the following 4 properties when introducing rxGroup:

Property #1: image. image allows you to specify a custom image or icon for the control.
Property #2: imageMso, which allows you to specify a built-in image or icon for the control.
Property #3: screentip, which you can use to specify a ToolTip string for the control.
Property #4: supertip, which allows you to set up an Enhanced Screen Tip.

All of these leaves the following 5 properties :

Property #1: description. description models an ST_LongString attribute within the Custom UI XML Markup Version 2 Specification. More precisely, description allows you to set a string that is the extended description of the relevant control. This description is displayed in menus with itemSize set to large.
Property #2: enabled. enabled is a Boolean property. You can use enabled to specify whether the control is enabled .
Property #3: showImage. showImage is a Boolean property. You use showImage for purposes of specifying whether to display the control's image .
Property #4: showLabel. showLabel is, again, a Boolean property. You can use showLabel to specify whether the control's label is displayed .
Property #5: size. size models an ST_Size attribute within the Custom UI XML Markup Version 2 Specification. You can use size to specify the size of the control (usually rxsNormal or rxsLarge).

Considering all of the above, I add the following statements to the sample Custom_Ribbon Sub procedure for purposes of adding a new button:

Dim myButton1 As rxButton Set myButton1 = New rxButton With myButton1 .imageMso = “_3DPerspectiveDecrease” .keytip = “L” .Label = “Last Row” .ScreenTip = “Find last row with data” .Size = rxsLarge .supertip = “Finds the last row with data in the active worksheet” End With myCommandGroup.Buttons.Add myButton1

Code for Step #5 to create custom Excel Ribbon

Let's take a look at each of the new lines of code:

Line #1: Dim myButton1 As rxButton

This line is substantially the same as that used in previous steps. It uses the Dim statement to declare a new object variable (myButton1). myButton1 is declared as of the rxButton object type.

Line #2: Set myButton1 = New rxButton

This line of code follows the same structure as that used in previous steps. It uses the Set statement to assign an object (a new rxButton) to an object variable (myButton1).

Lines #3 And #10: With myButton1 | End With

As the previous lines #1 and #2, code lines #3 and #10 are substantially the same as those used in previous steps. They open and close a With… End With block.

All of the statements within the block (lines #4 through #9 below) are executed on the object that the myButton1 object variable represents.

Line #4: .imageMso = “_3DPerspectiveDecrease”

This line of code uses the imageMso property for purposes of specifying the image that is used as icon for Button1.

Since the property that I use is imageMso (instead of image), the Sub procedure uses a built-in image. The identifier of the built-in image that I chose is “_3DPerspectiveDecrease”. This is the identifier of the following built-in image:

There is a huge amount of unique icons in Office 2013. The Ribbon Commander Framework allows you to benefit from the Dynamic Icon & Image Ribbon Browser add-in. This add-in allows you to easily search and use the appropriate names to use in your code .

Line #5: .keytip = “L”

Line #5 uses the keytip property for purposes of setting up a KeyTip for Button1. The KeyTip that I've chosen is the letter L.

As a consequence of this, and the Keytip of the Power Spreadsheets tab that I add in step #3 above, the complete keyboard shortcut for the command associated with Button1 is “Alt + Y2 + L”.

Line #6: .Label = “Last Row”

This line uses the label property . It sets the string “Last Row” as its value.

Line #7: .ScreenTip = “Find last row with data”

Line #7 sets a ToolTip for Button1. In this case, the string I choose as Tooltip states “Find last row with data”.

Line #8: .Size = rxsLarge

This line of code makes reference to the size property, which specifies the control's size . For Button1, I choose the value rxsLarge.

Line #9: .supertip = “Finds the last row with data in the active worksheet”

This line uses the supertip property for purposes of creating an Enhanced ScreenTip for Button1. The string that is displayed as Enhanced ScreenTip is “Finds the last row with data in the active worksheet”.

Line #11: myCommandGroup.Buttons.Add myButton1

Line #11 is substantially similar to other lines code used in previous steps. It uses the add method for purposes of adding myButton1 to the buttons collection .

Once you've completed this step #5, you've gone through the main elements you work with when customizing the Excel Ribbon:

The Ribbon itself in step #2 above.
Tabs in step #3 above.
Groups of Commands in step #4 above.
Buttons (or other controls) in the current step #5.

As you can probably imagine, you're almost done customizing the Ribbon. Let's move on to…

Step #6: Render The Context UI

The steps I describe above allow you to modify and customize the Ribbon.

However, for purposes of optimizing performance, the Ribbon Commander Framework carries UI updates in the following 2 steps:

Step #1: Update the rxCustomUI state.
Step #2: Render the updated UI.

If you've followed the previous steps in this tutorial, you've already updated the rxCustomUI state (step #1).

To render the updated UI (step #2), you use the refresh method of rxCustomUI. To do this in the sample Custom_Ribbon Sub procedure, I add the following statement:

myCustomUI.Refresh

Code for Step #6 to create custom Excel Ribbon

Strictly speaking, we're now ready to execute the Custom_Ribbon Sub procedure. However, I suggest that you wait until we're fully done with step #7 below before actually running the Sub procedure .

The following screenshots show the results of the customizations to be carried out by this sample code . Notice the following things”

The previous 6 steps show how you can create a custom Ribbon with tabs, Groups of Commands and buttons (or other commands). However, the work isn't complete…

In particular, up until now, the button (or other commands) added to the Ribbon don't do anything when clicked. The following step shows you how to fix and complete the creating of your customized Ribbon:

Step #7: Add A Callback

rxButton has 12 delegates. However, for purposes of adding a callback (to handle when a user clicks on a button) within this tutorial you only need to understand 1:

The onAction delegate of rxButton allows you to specify the name of the callback function that is called when there's a user action. You use the onAction delegate for purposes of specifying what happens when the user clicks the relevant button .

In other words:

This step is what allows you to execute a specified macro when a user clicks on the button .

To understand callbacks, it may help if you draw a parallel with VBA events. More precisely, as explained in Excel 2016 Power Programming with VBA :

VBA responds to user actions using events. The Ribbon uses a different technique: callback procedures.

Let's go back to the onAction delegate:

Within the Ribbon Commander Framework, delegates are:

Of the rxDelegate type. You create delegates by using the make_delegate method of rxCustomUI.
Always associated with (or owned by) and created via a unique rxCustomUI instance.

Therefore, for purposes of specifying what happens when the user clicks on a button, you proceed as follows:

Step #1: Create an rxDelegate object by using the make_delegate method.
Step #2: Use the onAction delegate to hook up the rxDelegate object you created in step #1 and the already existing button in the custom Excel Ribbon.

In the sample Custom_Ribbon Sub procedure, I do this by adding the following statement:

myButton1.OnAction = myCustomUI.make_delegate(“myCallback1”)

Code for Step #7 to create custom Excel Ribbon

This statement connects the onAction delegate with myCallback1 .

Notice that we haven't (yet) provided myCallback1. You can, however, do this in the following 3 easy steps :

Step #1: Right-Click On The Name String

Within the Visual Basic Editor , go to the relevant callback name string, and right-click on it. In this case, this string is “myCallback1”.

After you've done this, the Visual Basic Editor displays the following contextual menu:

Contextual Menu to add callback for custom Ribbon

Step #2: Select “Insert [rxButton.onAction] stub”

Click on the following items within the contextual menu:

“Ribbon Commander”.
“Insert Delegate Stubs”.
“Insert [rxButton.onAction] stub”.

How to Insert Delegate Stubs with Ribbon Commander

Once you do this, an empty delegate stub is automatically added . This looks roughly as follows (the name will vary depending on the callback name string you use):

Step #3: Modify The VBA Code Within The Delegate Stub

The delegate stub created by the Ribbon Commander raises an error and, therefore, it won't work.

You need to update the code by including the statements that you want to be executed when the button you've created is clicked on.

In this example, I include the following VBA code:

Dim myLastRow As Long myLastRow = ActiveSheet.Cells.Find(What:=”*”, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row MsgBox prompt:=”The last row is Row No. ” & myLastRow

VBA code for delegate stub in custom Excel Ribbon

The purpose of this VBA code is to:

#1: Find the last row in the active worksheet.
#2: Display a message box indicating the number of the last row with data.

I explain this particular macro, along with pretty much everything you need to know about finding the last row in Excel with VBA , in this VBA tutorial .

Now the custom Ribbon is ready . You can now execute the Sub procedure you've created to customize the Ribbon. Once you've done this:

The Excel Ribbon is customized in accordance with the code you've created using the Ribbon Commander Framework. You can see the Ribbon that results after executing the sample Custom_Ribbon Sub procedure above.
Once a button within the custom Ribbon is clicked by a user, it is appropriately handled. In the case of the example that I show throughout this tutorial, once the Last Row button is clicked: (i) the last row in the active worksheet is found and (ii) a message box displays its number.

To test whether this is working properly, I type “Test for macro customizing the Excel Ribbon” in cell B34 of the example workbook that accompanies this Excel tutorial.

Excel worksheet to test custom Excel Ribbon

This Excel VBA Custom Ribbon Tutorial is accompanied by an Excel workbook containing the macros I use . You can get immediate free access to this example workbook by subscribing to the Power Spreadsheets Newsletter .

The following image shows the message box that Excel displays after I click on the Last Row button. Notice that, as expected, the message box displays the number of the last row with data (34):

Excel dialog box with results for custom Excel Ribbon

Strictly speaking, the 7-step process I describe above is enough for you to start creating custom Excel Ribbons with the Ribbon Commander Framework . However, if you want to make your code more compact, you may be interested in…

Step #8 (Optional): Make Your Code More Compact

The Ribbon Commander API is designed to take advantage of the With… With End VBA construct .

Therefore, whenever you use the Add method to add a particular object to a collection, the method returns the added object.

As a consequence of the above, you can write your code using a different structure from the one I use in the previous steps of this tutorial by relying on:

The Add method.
The With… End With statement.

For example, the sample Custom_Ribbon Sub procedure that we developed above can be rewritten by using a series of nested With… End With blocks as follows:

Block #1 , works with the myCustomUI variable created to reference the rxCustomUI (as explained in step #1 above). Additionally, just before the End With statement that ends the With… End With block, it renders the updated UI (the rough equivalent of step #6 above).
Block #2: Creates a new tab, the rough equivalent of step #3 above.
Block #3: Creates a new Group of Commands, the rough equivalent of step #4 above.
Block #4: Creates a new button, the rough equivalent of step #5 above.

The following Sub procedure (Custom_Ribbon_Compact) shows this:

Reset The State Of The Ribbon

The Sub procedure structure that I use throughout this Excel tutorial creates a new tab (labeled “Power Spreadsheets”) every single time you execute the Sub procedure. This is probably not what you want.

Furthermore, in other cases, you may want to clear the old state of the Ribbon and reset the default rxCustomUI.

To do this, you just need to proceed as follows :

Step #1: Use the clear method of rxCustomUI, to clear the control's state.
Step #2: Use the refresh method of rxCustomUI to render the updated UI.

The following sample macro (Clear_Custom_Ribbon) does this:

After completing this Excel tutorial, you're ready to start crafting code that customizes the Excel Ribbon . In addition to knowing how to create a custom Excel Ribbon in 7 (or 8) steps, as well as how to reset the state of the Ribbon, you have a good understanding of the following topics :

The main components of the Excel Ribbon.
The most common approaches to customizing the Excel Ribbon, and why they're less powerful and/or more complicated than using the Ribbon Commander.
What is the Ribbon Commander Framework, and why this is a better approach (vs. the more common ones) to use when you want to customize the Excel Ribbon.
What is XML.
How is the Ribbon Commander object model organized, and how you can use it to customize the Ribbon.

Books And Resources Referenced In This Excel Tutorial

Alexander, Michael and Kusleika, Dick (2016). Excel 2016 Power Programming with VBA. Indianapolis, IN: John Wiley & Sons Inc.
Jelen, Bill and Syrstad, Tracy (2015). Excel 2016 VBA and Macros. United States of America: Pearson Education, Inc.
Mansfield, Richard (2016). Mastering VBA for Microsoft Office 2016. Indianapolis, IN: John Wiley & Sons Inc.
Martin, Robert, et. al. (2008). RibbonX: Customizing the Office 2007 Ribbon. Indianapolis, IN: John Wiley & Sons Inc.
Walkenbach, John (2015). Excel 2016 Bible. Indianapolis, IN: John Wiley & Sons Inc.
Walkenbach, John (2013). Excel VBA Programming for Dummies. Hoboken, NJ: John Wiley & Sons Inc.

The following links are affiliate links, which means that if you choose to make a purchase, I will earn a commission. This commission comes at no additional cost to you.

30-day FREE trial of the Ribbon Commander Framework ; or
1-year subscription for the Ribbon Commander Framework

I publish a lot of Tutorials and Training Resources about Microsoft Excel and VBA . Here are some of my most popular Excel Training Resources:

Free Excel VBA Email Course
Excel Macro Tutorial for Beginners
Excel Power Query (Get and Transform) Tutorial for Beginners
Excel Keyboard Shortcut Cheat Sheet
Excel Resources

How-To Geek

How to add new buttons to the microsoft office ribbon.

Save time digging in Microsoft Office menus by adding buttons to the ribbon bar in Word, PowerPoint, Excel, and more. Here's how.

The Microsoft Office ribbon contains most of the commands you need, but sometimes it would be useful to add to it. Here's how to find and choose other buttons to add to the ribbon in whichever tab you want.

The ribbon bar has been standard in all of the Office apps---Excel, OneNote, Outlook, PowerPoint, and Word (plus Project and Visio if you paid for them)---since Office 2007, and it's fair to say it's pretty useful. Microsoft has put a lot of work into making the right tab open automatically depending on what you have selected in the app, and it's so seamless most people don't even notice.

Related: How to Show the Ribbon in Microsoft Excel, Word, and Outlook

Still, everyone has different needs, and you might find a ribbon button more useful than diving through a couple of menus or using a context menu. You can add any app command as a button in the ribbon using a simple customization process.

We're going to show you how to do this using Outlook, but the same instructions apply to all the Microsoft Office apps. As an example, we're going to add a new button to the Home tab in the Outlook ribbon to create a new email in plain text.

Right-click on any of the tabs in the ribbon bar, and select "Customize The Ribbon."

In the "Customize The Ribbon" panel that opens, change the "Popular Commands" drop-down menu to "All Commands."

Scroll down until you reach the command you want. In this case, we're going to select "Plain Text."

To add your button to the ribbon, you have to add it to a group. These are shown in the right-hand column.

We want to add our button to the "Home" tab and in its own group. (Although you can add a command to an existing group by simply selecting that group.)

To add a group, click the "New Group" button and then click "Rename" to give the group a suitable name. Select the "OK" button to confirm the group's creation.

We want our button to be the first button in the tab, so it needs to be moved to the top of the list, above the "New" group. Use the arrows on the right-hand side to move your new group to the top of the list, or drag and drop it into position instead.

The final step is to add the button to the group. Select "Plain Text" in the left-hand panel, and click the "Add" button to add it to the group.

Click "OK" to close the panel. Your new group, containing the "Plain Text" button, will be visible in the "Home" tab.

To remove the button, right-click the tab and select "Customize The Ribbon" again. Select the button in the right-hand panel, then select "Remove."

Click "OK" and the button will be removed from the ribbon. If the group you created only had one button in it, the group will be removed as well.

You can add as many groups and as many buttons as you like, as well as removing the default buttons and groups. This makes the ribbon as customizable as you could possible want.

If you want to remove customizations from a ribbon tab, click the "Reset" button found in the "Customize The Ribbon" menu, then select "Reset Only Selected Ribbon Tab."

Alternatively, if you want the entire ribbon to be reset to the default, use "Reset All Customizations" instead. The various reset options allow you to make as many changes as you want with confidence, because you're only ever a button press away from restoring a default tab or the entire default ribbon.

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:

<tabs>

<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
Ελληνικά
Русский
עברית
العربية
ไทย
한국어
中文(简体)
中文(繁體)
日本語

How to Strikethrough in Excel? Easy Ways and Advanced Tips

Updated on May 16, 2024

Strikethrough formatting lets you show that a task is done, that data is no longer valid, or that information should no longer be considered. But how to add this strikethrough in Excel ? Here’s a complete guide for you.

Table Of Content

1. How to strikethrough in excel? 2. Strikethrough Shortcut in Excel 3. Partial Formatting with Strikethrough 4. Strikethrough with Conditional Formatting 5. How to Automatize Strikethrough Formatting? 6. Conclusion

1. How to strikethrough in excel?

You can easily add strikethrough formatting in Excel with some basic steps.

Excel-Formulas-Practice-Someka-Template-Banner

Step 1: Select the relevant cell(s)

Firstly, you should choose the cell or cells that you want to change. So, either click on a single cell or click and drag your mouse over several cells to select them all.

Step 2: Click on the Format Cells box

Secondly, you’ll open the format cell dialogue box. Therefore, you have three options: 1. Ribbon, 2. Right-click menu, 3. CTRL+1 Shortcut.

Ribbon Method: On the Excel ribbon, go to Home > Cells > Format Cells .

Right-Click: Right-click on the cell(s) you want to change and select “ Format Cells ” from the menu that appears.
Shortcut on Keyboard: The Format Cells dialogue box will quickly appear when you press Ctrl + 1 .

Step 3: Check the box next to Strikethrough

Lastly, on the Format Cell dialogue box, under the Fonts tab you can find Strikethrough checkbox under the effects.

So, once you’ve chosen this option, click “OK” to give the selected cells the strikethrough look.

2. Strikethrough Shortcut in Excel

As many actions, strikethrough has also a shortcut in excel.

After selecting the cell or range you want to strikethrough, just click on CTRL+5 on your keyboard. This will put a line on the text directly.

3. Partial Formatting with Strikethrough in Excel

The above methods strikethrough the entire text in a particular cell. But, there are times when you might only want to strikethrough a small part of the text in a cell and not the whole thing.

That’s also possible:

First, dive into the cell with double-click it or press F2
Secondly, select the part you want to strike through text in a cell

partial strikethrough in a cell in excel

Thirdly, open Format Cell window with CTRL 1
Then, go to Font > Effects > Strikethrough
Lastly, click OK

This method lets you keep the formatting of your text flexible, so you can highlight or underline certain parts of the cell content.

4. Strikethrough with Conditional Formatting

With conditional formatting in Excel, you can change the way something looks based on certain conditions. This is helpful for strikethrough formatting because it can do it automatically based on the state of your data.

For example, we want to strikethrough the tasks that are DONE.

Firstly, select your tasks range, and go to Home > Conditional Formatting > New Rule .

We’ll use a formula to manage our rule.

Conditional Formatting to Strike Through Done Tasks

This formula says that if the adjacent cell (here it’s in the C column) is “DONE”, strikethrough the selected range (in this case it is in the B column).

Lastly, checkmark the Strikethrough option on the formatting window.

Now you have created a dynamic conditional formatting for your tasks.

You can also use strikethrough option with Excel checkboxes .

In this time, you’ll build your conditional formatting rule formula to check if the adjacent cell is TRUE or not.

This will create a checklist where the item turns stroked when the checkbox is marked.

5. How to Automatize Strikethrough Formatting?

If you’re using this strikethrough formatting feature too often, you might add a button to add strikethrough texts at one click.

You can add such buttons with macro, Excel Ribbon or Quick Access Toolbar.

a. Strikethrough button with a macro

You can use strikethrough formatting with just one click if you make a macro.

Open your VBA editor with Alt+F11
Go to Insert > Module to add a new module
Add the below code and close your editor
Insert a shape as your button and name it
Right click on the shape and select Assign macro from the menu
Select your strikethrough macro and click ok.

Now when you click on this button, the click cell or range will have strikethrough formatting.

b. Adding a strikethrough button to your Excel Ribbon

You can add a button to your Excel Ribbon for the strikethrough macro to make it even easier to use:

Right click any spot on your ribbon
Click Customize Ribbon

Next, on the Excel Options window , choose a tab (in the below example the home tab is selected) and click “ New Group ”

Then find the strikethrough command under the commands list and click Add.

add strikethrough command to excel ribbon

And click OK to save your changes. So, now you have a direct access from your ribbon.

c. Adding a Strikethrough button to Quick Access Toolbar

Again open your Excel Options window. Then, go to Quick Access Toolbar from the left-side menu. Secondly, find the Strikethrough command under the list and click on add.

add strikethrough command to excel quick access toolbar

Lastly, click on OK and save this.

Excel Quick Access Toolbar With Strikethrough Button

Now you have a strikethrough button on your Quick Access Toolbar .

That’s all for the adding automatization buttons for Excel strikethrough formatting.

6. Conclusion

Finally, the strikethrough is a flexible formatting tool in Excel that can help you better manage and arrange your data.

Thus, we have tried to explain you how to strikethrough your entire or partial text in a cell or in a range. Then, we have also given you the strikethrough shortcut so that you can apply it without using your mouse. Finally, we have also given some advanced tips on adding direct buttons for strikethrough command.

So, hope you enjoy our article on Strikethrough in Excel!

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.

IMAGES

How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar
How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar
How to insert a macro button to run macro in Excel?
How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar
How to insert a macro button to run macro in Excel?
How to enable macro in excel 2016

VIDEO

Excel VBA
How To create Excel Ribbon from Macro with Custom UI Editor- Boost Excel
How To Record Macro Permanently in Ms Excel
How to insert a Button that works with VBA Macros in Excel
Using a Button to run a macro in Excel
Excel Tips: Creating a Macro Button

COMMENTS

How to Add Macro Buttons to the Excel Ribbon or Quick ...
Adding Macro Buttons to the Excel Ribbon. To add a button to the ribbon, start by right-clicking anywhere on the ribbon or ribbon tabs. Then select Customize the Ribbon. This will open the Excel Options page, and Customize Ribbon should already be highlighted on the left-hand side. In the right-hand side of the Excel Options page, you will see ...
Assign a macro to a Form or a Control button
Add a button (Form control) On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Button . Click the worksheet location where you want the upper-left corner of the button to appear. The Assign Macro popup window appears. Assign a macro to the button, and then click OK.
Add a Button and Assign a Macro in Excel
To record a new macro and assign it to the button, click "Record…". This brings up the Record Macro dialog, where you specify a name and click "OK". The button will be assigned that macro. Meanwhile, Excel will remain in a recording state until you click "Stop Recording" in the "Code" section of the Developer tab.
Assign a macro to a button
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 ...
How to Add Your Excel Macros to Custom Ribbon Tab
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.
How to run macro in Excel and create a macro button
And then, do the following: On the Developer tab, in the Code group, click Macros. Or press the Alt + F8 shortcut. In the dialog box that shows up, select the macro of interest, and then click Run . Tip. If the Developer tab is not added to your Excel ribbon, press Alt + F8 to open the Macro dialog.
How to Create Macro Buttons in Excel Worksheets
Here are the steps to create the macro button: 1. Draw a shape on the sheet (Insert tab > Shapes drop-down > Rectangle shape). 2. Add text to the shape (Right-click > Edit Text | or double-click in the shape). 3. Assign the macro (Right-click the border of the shape > Assign Macro…) 4. Select the macro from the list.
How to Assign a Macro to a Button in Excel (Easy Guide)
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. In the options that appear, in the ActiveX Controls options, click on the Command Button option. Click anywhere on the worksheet.
How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar
Sign up for our Excel webinar, times added weekly: https://www.excelcampus.com/blueprint-registration/Learn how to add custom macro buttons to the Excel Ribb...
How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar
Learn how to add custom macro buttons to the Excel Ribbon or Quick Access Toolbar.This allows you to run macros from your personal macro workbook at any time...
Excel VBA: Adding custom Button to the Toolbar or Ribbon
If used in newer versions, from Excel 2007, the button and commandbar will be added to the "Add-Ins" tab. It is only possible to add buttons to the main ribbon using dynamic XML when using VBA. See below for more information. ' Constants. Private Const COMMANDBAR_NAME As String = "Custom Toolbar". Private Const BUTTON_CAPTION As String ...
Customize Excel ribbon with your own tabs, groups or commands
Here's how: In the Customize the Ribbon window, under the list of tabs, click the New Tab button. This adds a custom tab with a custom group because commands can only be added to custom groups. Select the newly created tab, named New Tab (Custom), and click the Rename… button to give your tab an appropriate name.
4 Ways to Create a Custom Macro Button in Excel
7. Select Macros from the list on the left. 8. Click and drag the Custom Button icon from the list on the right to your new toolbar. The new button is represented by a smiley face. 9. Right-click on the newly added button. 10. Rename the button to your liking or leave the default name in the Name: field.
Custom Excel Ribbon In 7 Easy Steps: The Introductory Tutorial
The Ribbon, as mentioned in Excel 2016 Power Programming with VBA, is organized in a hierarchy where: Tabs are across the top of the Ribbon. Tabs consist of Groups of Commands. Groups of Commands are composed of buttons and other controls. The following sections show each of these items. Ribbon Tabs. The Excel Ribbon groups commands in ...
How to Add New Buttons to the Microsoft Office Ribbon
The final step is to add the button to the group. Select "Plain Text" in the left-hand panel, and click the "Add" button to add it to the group. Click "OK" to close the panel. Your new group, containing the "Plain Text" button, will be visible in the "Home" tab. To remove the button, right-click the tab and select "Customize The Ribbon" again.
Excel Add VBA Button
Adding a Form Control Button. In the Ribbon, select Developer > Insert > Form Controls > Button. Click and drag in the worksheet to create a button. As soon as you release the mouse button, the assign macro dialog box will appear. Scroll down to 1) select the macro you wish to assign to the button, and then, 2) click OK.
How To Add A Macro To A Button In Excel 2016
How To Add A Macro To A Button In Excel 2016In the first episode I taught you how to add a button into Excel. If you need to watch that video first, you can ...
excel
The Developer tab is not visible by default so it needs to be enabled in File>Options>Customize Ribbon. On the Developer tab, click the Visual Basic button. The code editor will launch. Right click in the Project Explorer pane on the left. Click the insert menu and choose module.
Add or edit a macro for a control on a worksheet
Add or edit a macro for an ActiveX control. If the Developer tab is not available, display it. Click File > Options > Customize Ribbon. Under Customize Ribbon, select the Developer check box, and then click OK. Click the Microsoft Office Button , and then click Excel Options.
How to add custom macro buttons with custom icons to the Ribbon/QAT
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.
vba
This is based on Ron de Bruin's code "Enable or Disable custom ribbon buttons". The following example ribbon consists of two tabs: "Tools": empty for now "Macros": 3 groups All buttons ...
checkboxes
Click "OK" to save your changes and close the Excel Options dialog box. Adding a Checkbox. Once the "Developer" tab is visible on the ribbon, you can add checkboxes to your worksheet: Go to the Developer Tab: Click on the "Developer" tab in the ribbon. Insert Checkbox: Click on the "Insert" button in the "Controls" group.
Strikethrough in Excel
Now when you click on this button, the click cell or range will have strikethrough formatting. b. Adding a strikethrough button to your Excel Ribbon. You can add a button to your Excel Ribbon for the strikethrough macro to make it even easier to use: Right click any spot on your ribbon; Click Customize Ribbon
Add or edit a macro for a control on a worksheet
Add or edit a macro for an ActiveX control. If the Developer tab is not available, display it. Click File > Options > Customize Ribbon. Under Customize Ribbon, select the Developer check box, and then click OK. Click the Microsoft Office Button , and then click Excel Options.
Assign a macro to a button
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 ...

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

Insert a Shape and Assign Macro to that Shape

Keeping Shape Visible When you Hide/Resize Rows/Columns

Assign a Macro to Form Control Button

Assign a Macro to an ActiveX Control Button

1 thought on “How to Assign a Macro to a Button in Excel (Easy Guide)”

Leave a Comment Cancel reply

Excel VBA: Adding custom Button to the Toolbar or Ribbon

One thought on “ Excel VBA: Adding custom Button to the Toolbar or Ribbon ”

Leave a Reply Cancel reply

Related Posts

Attendance Tracker Template for Microsoft Excel

Employee Absence tracking Template for Microsoft Excel

Excel VBA: Get Employee information from Active Directory

Privacy Overview

Customize Excel ribbon with your own tabs, groups or commands

Excel ribbon: what can and cannot be customized

What you can customize

What you can't customize

How to customize ribbon in Excel

How to create a new tab for the ribbon

How to add a custom group to a ribbon tab

How to add a command button to Excel ribbon

Show icons instead of text labels on the ribbon

Rename ribbon tabs, groups and commands

Move tabs, groups and commands on the ribbon

Remove groups, custom tabs and commands

Hide and show tabs on the ribbon

Customize contextual tabs on Excel ribbon

How to reset Excel ribbon to default settings

How to export and import a custom ribbon

You may also be interested in

12 comments

How to Create a Custom Macro Button in Excel

Expert Q&A

You Might Also Like

About This Article

Is this article up to date?

Featured Articles

Trending Articles

Watch Articles

Custom Excel Ribbon In 7 Easy Steps: The Introductory Tutorial

Excel Ribbon, Tabs, Groups And Buttons

The Excel Ribbon

Ribbon Tabs

Groups Of Commands

Buttons And Other Controls

Custom Excel Ribbon: The Basics

Extensible Markup Language (XML): A Brief Introduction

Download, Install And Activate The Ribbon Commander Before Beginning

Step #1: Download The Ribbon Commander

Step #2: Install The Ribbon Commander

Step #3: Activate The Ribbon Commander

Introduction To The Ribbon Commander Object Model

Properties, Methods, Delegates And Events In The Ribbon Commander Framework

Create A Custom Excel Ribbon Using VBA: Example

Step #1: Create A Reference To rxCustomUI

Line #1: Dim myCustomUI as rxCustomUI

Line #2: Set myCustomUI = rxCustomUI.defaultInstance

The Create Method

Step #2: Create A Reference To The Ribbon

Step #3: Create A New Tab

Group #1: ID Properties (3 Properties)

Group #2: Insertion Location Properties (4 Properties)

Group #3: Read-Only Properties (2 Properties)

Group #4: Other Properties (6 Properties)

Line #1: Dim myTab As rxTab

Line #2: Set myTab = New rxTab

Lines #3 And #6: With myTab | End With

Line #4: .keytip = “Y2”

Line #5: .Label = “Power Spreadsheets”

Line #7: myRibbon.tabs.Add myTab

Step #4: Create A New Group Of Commands

Line #1: Dim myCommandGroup As rxGroup

Line #2: Set myCommandGroup = New rxGroup

Lines #3 & #7: With myCommandGroup | End With

Line #4: .autoScale = rxTrue

Line #5: .CenterVertically = rxFalse

Line #6: .Label = “Example Macros”

Line #8: myTab.groups.Add myCommandGroup