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.
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.
Follow these steps to get started with the Custom Ribbon tab.
After you open the My Macros sample file, you should see a custom tab on the Excel Ribbon -- MY MACROS. The custom tab has buttons and drop down lists, described below.
There are 10 buttons at the left, in 2 groups - Macro Buttons1 and Macro Buttons2.
To test the macros, click the Hello Message button, at the left side of the MY MACROS tab.
That button runs a macro that shows a message box, with the word, "Hello".
Click the OK button to close the message box.
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.
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.
To run different macros from the MY MACROS Ribbon tab, you will make changes on the Admin sheet in the sample workbook.
The Button information is stored in the table at the left side of the Admin sheet.
In the second row, you can see the settings for Btn2. It has the label "Hello Message", and runs the HelloMsg macro. It displays the image named WebGoBack.
At the right, there are two Excel tables. They contain the macro names for each of the drop down lists.
In Macro List 1, you can see the ToggleGridlines macro, which you tested earlier.
The drop down lists are flexible, and will show all the items from the Macro List tables on the worksheet. You can delete items from the worksheet lists, add new items, or change the existing macro names.
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:
Next, you'll add another one of the sample macros to the Macro List 1 drop down.
There are 10 buttons set up in the MY MACROS tab. They can be customized, but there are some restrictions on the button changes.
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.
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
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:
(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
After you finish adding your macros, go to the Admin sheet and add your macro names.
Remember to save your file, after making the changes
In the Button list, an image name is assigned to each of the buttons. After you add your own macros to the workbook, you might want different images, to match the actions in your macros.
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:
Another way to find built-in button images is with Image Gallery addins. For example:
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.
You can create your own add-ins, by storing macros in a file, then saving it as an add-in:
After you have saved your file as an add-in, follow these steps to install it.
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:
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.
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.
Thanks to the following people, who have shared their knowledge about Excel Ribbon customization.
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.
Last updated: October 21, 2020 4:03 PM