Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Dynamic Menu for your favorite macros in Excel 2007-2016

If you want to use your favorite macros in all your workbooks then you can copy the macros in your Personal.xlsb. See this page for info: How do I create and use a PERSONAL file for my VBA code

Create your own tab on the Ribbon in the userinterface(only in 2010-2016) or with RibbonX in(2007-2016), see this page for RibbonX examples Change the Ribbon in Excel 2007-2016

And another option is to use a add-in with a popup menu in the Quick Access Toolbar (QAT), like I show you on this page : Menu for favorite macros in Quick Access Toolbar in Excel 2007-2016.

Or you can use the Add-in below that create a Dynamic menu on the Home tab

 

How do I use the Dynamic menu Add-in

The add-in add a smiley button to the Home tab that opens a Dynamic menu. The RibbonX that create the menu is stored in a txt file and I add a workbook to the download that makes it very easy to edit the menu and choose a imageMso for a button. Much easier then making a custom Ribbon with RibbonX.

1) Download the zip file with the add-in,txt file and help workbook DynamicMenuWindows.zip (6-Aug-2018), this is the first version with the menu info in a txt file so let me know if you have problems.

2) Unzip/Copy the 3 files to a folder on your system (the 3 files must be in the same folder).

3) Use the shortcut Alt ti or click the Microsoft Office button(2007) or File(2010-2016), click Excel options(2007) or options(2010-2016), click the Add-Ins tab. In the Manage drop-down, click Excel Add-ins, and click Go.

Use "Browse" to select the add-in and then click on OK. Verify DynamicMenu-Win_Excel is checked in the add-in list and then click OK.

4) If we click on the RDB Menu button on the Home tab now the Dynamic menu will PopUp. Note: If you not see the menu option close Excel and right click on the add-in file and choose propeties and unblock it.

Note : Be sure that you not have "Show Add-in user interface errors" checked in File>Options>Advanced>General, if you do you get errors because 2007 not know about some new imageMso's that Microsoft add in the newer Excel versions.

 

How do I edit the menu :

Open the Workbook(EditDynamicMenu.xlsm)

Note: This workbook is only if you want to edit the menu

After you open the help workbook there is a new tab next to the Home tab on the Ribbon named “Edit My Menu” with a few menu options. There is an option to add buttons below the activecell row and to Start a menu and End a menu and to delete the activecell row. If you have select a cell in the imageMso column you can look for the imageMso(3745) you want for that button in the Ribbon menu and if you click on the imageMso that you want then the name will be placed in the cell.

Look at the Table and how the menu looks like and you see that it is very easy.

When you have edit the menu you must first press the “Update menu txt file” button before you can press the Smiley icon to see if the menu is correct.

After it looks correct you can close the workbook and open the VBA editor to add your macros in the MacroModule of the add-in, look at the macros that are already in it so you know how you call a macro from a Ribbon button.

Feedback is welcome