Instead of struggling to build your own custom tab for the Excel Ribbon, download my sample file. It has a pre-built tab, and you can put your Excel macros on this custom tab, by making simple changes on a worksheet.
Watch the Video
To see how to add your macros to the ribbon, download the sample workbook, and watch this video.
It shows the steps for using the custom tab, editing the worksheet, finding button images, and saving as an add-in.
How It Works
When you open the sample workbook, and enable macros, you’ll see a custom tab – MY MACROS.
That tab has 10 buttons that you can customize, and 2 drop down lists where you can add your macro names.
Macro Lists
In the workbook, go to the Admin sheet, to see the current settings for the buttons and drop down lists.
The workbook has a few sample macros, to show how the buttons and lists work. The macros in Macro List 1 appear in the first drop down on the MY MACROS tab.
You can add or remove macro names in those lists, to change the drop down lists in the custom tab. I haven’t tested the limit, but you should be able to add most of your favourite macros there.
Change the Buttons
There are 10 buttons on the MY MACROS tab, and you can customize the labels, macro names and button images.
Use the buttons to run the macros that you use most often, so they’re available with a single click.
For example, change the settings for the third button, so it runs a macro that creates a list of colour samples.
Then, close Excel and re-open it, and the button shows the new label and image, and runs that macro.
Create an Add-in
The sample workbook is in xlsm format (macro-enabled. Follow the instructions to add your own macros, and edit the button and drop down setting.
Then, with a few simple steps, save the workbook as an add-in, and install it in Excel.
After that, you will see the MY MACROS tab in all your open workbook, and you can use those macros everywhere.
Get the Sample File
Go to my Contextures website, to get the free workbook with macro buttons and drop down lists.
The zipped file is in xlsm format, so be sure to enable macros when you open the workbook.
_______________________
________________
Dear Debra,
I read your blog and your sample file has been applied to my Excel application program. It works well but occasionally I encountered VBA Runtime Error 1004 “Application-defined or Object-defined error” at “RibbonMacros” Module.
Error Code Line:
Count = ThisWorkbook.Names(strList) .RefersToRange.Rows.Count
where,
strList = “Macro2List”
ThisWorkbook.Names(strList).Index = 1622
Please check the code and advise me the correction.
Best regards,
Steve
PS: it should be highly appreciated to let me know how to remove it after trial.
Dear Debra,
As I wrote here above on September 23, 2020, I have added Macros to Ribbon Tab called “MY MACROS”.
Now I am asked to send the EXCEL file but the Ribbon Tab contains too many trial Macros.
I have to delete the Tab completely. I am using VB7.
Your kind and prompt reply should be highly appreciated.
Best Regards,
Steve