Excel Ribbon Macros Tab

Quickly add your macros to buttons and drop down lists on a custom Excel Ribbon tab. Get the free workbook, and make easy changes, so your macro tab shows for all files.

Introduction

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

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

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

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

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

Open the Sample File

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

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

   

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

Test the Custom Tab

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

Buttons

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

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

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

Click the OK button to close the message box.

Drop Down Lists

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

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

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

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

Excel Help

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

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

Admin Sheet

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

Buttons

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

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

Drop Down Lists

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

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

Add Macro to Drop Down

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

Get Ready to Add a Macro

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

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

   

Add Another Macro

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

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

   

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

   

Change a Button Macro

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

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

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

Change a Button

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

1. In the Buttons list, change the settings for Button 3 to:
   • Label: Colour List
   • Macro: ColourList
   • Image: FontColorCycle

   

2. The Button on the Ribbon will change the next time that you open the workbook
   • Save the My Macros sample file
   • Close the My Macros sample file, and then re-open it.
3. Button 3 should now show its new label and image. Click the Colour List button, to add a new sheet to the workbook, with a list of colours.

   

Add Your Own Macros

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

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

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

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

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

   

4. When you’re finished, close the VBE, and return to Excel

Sample Macro Code

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

Sub ListAllNames()
Dim wb As Workbook
Dim ws As Worksheet
Dim nm As Name
Dim lRow As Long
Set wb = ActiveWorkbook

If wb.Names.Count = 0 Then
  MsgBox "No names in this workbook"
  Exit Sub
End If

lRow = 1
Set ws = Worksheets.Add
  With ws
    .Cells(lRow, 1).Value = "Name"
    .Cells(lRow, 2).Value = "RefersTo"
    lRow = lRow + 1
    For Each nm In ActiveWorkbook.Names
      .Cells(lRow, 1).Value = nm.NameLocal
      .Cells(lRow, 2).Value = "'" & nm.RefersTo
      lRow = lRow + 1
    Next
    .Columns("A:B").EntireColumn.AutoFit
  End With
End Sub

Update the Admin Sheet

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

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

Remember to save your file, after making the changes

Find Button Images

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

Ribbon Commands

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

Follow these steps to find different images for your buttons:

1. Right-click on a blank part of the Excel Ribbon, and click Customize the Ribbon
2. By default, Excel shows a list of Popular Commands
3. To see different commands, click the arrow for the “Choose commands from” drop down
4. Choose Commands Not in the Ribbon — I like to use these images, because they’re different from the ones that are already in use

   

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

   

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

Image Gallery Add-ins

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

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

Create an Add-in

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

Save File as Add-In

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

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

Install Your Add-in

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

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

   

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

   

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

Make Changes to Your Add-in

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

1. In Excel, press Alt+F11, to open the Visual Basic Editor (VBE)
2. In the Project Explorer list, find the MyMacrosCustomTab project
3. Click the + beside Microsoft Excel Objects, to see the objects
4. Click on ThisWorkbook, to see its properties in the Properties window
5. In the Properties list, scroll down to find the IsAddin property
6. Change the IsAddin property to False

   

7. Go back to Excel, and the workbook’s sheets will be visible again
8. Make your changes to the settings on the Admin sheet

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

Save the Add-in Changes

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

1. In Excel, press Alt+F11, to open the Visual Basic Editor (VBE)
2. In the Project Explorer list, find the MyMacrosCustomTab project
3. Click the + beside Microsoft Excel Objects, to see the objects
4. Click on ThisWorkbook, to see its properties in the Properties window
5. In the Properties list, scroll down to find the IsAddin property
6. Change the IsAddin property to True
7. Click the Save button at the top of the VBE window
8. Close VBE, and go back to Excel

Excel Ribbon Resources

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

Download the Sample File

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

Related Tutorials

Excel
Ribbon — Getting Started

Excel Ribbon – Custom Tab

Customize
Quick Access Toolbar (QAT)

Create a
UserForm With ComboBoxes

VBA Code, Copy to a workbook

Dashboard Software