Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Ribbon Examples files and Tips

Follow the steps on the page first before you start with the examples and tips on this page : Change the Ribbon in Excel 2007-2013

Download RibbonX examples workbooks

You can download 8 Excel files here with basic RibbonX examples for Excel 2007-2013.
Download Example Excel files Last Updated : 17-March-2013

1: Add custom group in built-in tab.xlsm
2: Custom tab for Favorite Groups and buttons.xlsm
3: Custom tab for your favorite macros part 1.xlsm
4: Custom tab for your favorite macros part 2.xlsm
5: Dictator(1).xlsm
6: Dictator(2) With_Custom_Tab.xlsm
7: Disable-Visible-Repurposing.xlsm
8: Office Button or Backstage.

When you create your menus with Application.CommandBars in Excel 97-2003 you have code
to create the menu and delete the menu. In Excel 2007-2013 when you use RibbonX to customize the Ribbon this is different, when you close or deactivate the workbook it will automatic remove your customizations.

The files are working OK in Excel 2007-2013.
If you want to load different RibbonX for Excel 2007 and Excel 2010-2013 see :Load different RibbonX when opening file in Excel 2007 or 2010/2013

Note: For example files for changing Backstage(File) visit my Backstage page

 

Find the Control, Group and Tab names

You see in the RibbonX that I use for example idMso="VisualBasic" to point to a built-in control.
But how do you find the names of the controls ?

Download this file from the Microsoft site: Office 2007: Lists of Control IDs

Download this file from the Microsoft site: Office 2010: Office Fluent User Interface Control Identifiers

Download this file from the Microsoft site: Office 2013: Office Fluent User Interface Control Identifiers

I create my own versions of the files for Excel above to make it easier.
Note: If you disable macros you can't use the files.

Download the Excel 2007 version

Download the Excel 2010 version

Download the Excel 2013 version, wil create this when I have time

Tip: ContextMenu Add-in for Office 2010
This add-in from Microsoft add the name of the ContextMenu in every Context menu(Right click menu's).
This is a very easy way for developers to find the idMso of the menu so they can use it in the RibbonX.
Note: Only In 2010 and up you can change the ContextMenu's with RibbonX
http://archive.msdn.microsoft.com/contextmenusaddin

 

Find the names of the button images

You can see in the RibbonX that I use imageMso="HappyFace" to use the built-in image I want.
But how do you find the names of all the images?

Download this file from the Microsoft site Office 2007 : Icons Gallery

Download this file from the Microsoft site Office 2010 : Icons Gallery

Download this file from the Microsoft site Office 2013 : Icons Gallery

 

Or try this two add-ins from Ken Puls and Jim Rech

Identifying your imageMso – Excel, Word & Powerpoint Add-in from Ken Puls, It’s labelled as 2007, but still works in 2010 and 2013, maybe it is safer to use the 2007 images so its backwards compatible.

Control Image Add-in for only Excel 2007 and Excel 2010 (made by Jim Rech). Jim's add-in displays the images on buttons in the Ribbon (by adding a new tab), he has tried to remove most or all duplicate images and, in the case of Excel 2007, added a few images not in the MS list.
Download it from my site : File date (16-Nov-2010), also working in the 64 bit version of Excel 2010.

Note: Jim describes on the add-in's worksheet how he got the image names that he uses.
If you are curious change the IsAddin property of the add-in to False to see the worksheet.

 

Edit the Ribbon with a Add-in

Try this Add-in : RibbonX Visual Designer Add-in (made by Andy Pope)
http://www.andypope.info/vba/ribboneditor.htm

 

 Hide/Show the Ribbon/QAT with VBA

You can use this VBA example posted by Jim Rech to Hide/Show the whole Ribbon and QAT.

Sub HideRibbon()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
End Sub

Sub ShowRibbon()
    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
End Sub

Only in Excel 2010-2013 you can use this to toggle the view of only the Ribbon
CommandBars.ExecuteMso "MinimizeRibbon"

You can use a function to check the state of the Ribbon in 2007-2013

Sub test()
'Hide Ribbon if it is on the screen in 2010-2013
If RibbonState = 0 Then CommandBars.ExecuteMso "MinimizeRibbon"
End Sub

Function RibbonState() As Long
'Sascha Trowitzsch: http://www.mosstools.de/
'Result: 0=normal, -1=autohide
    RibbonState = (CommandBars("Ribbon").Controls(1).Height < 100)
End Function

 

Hide the MRU("Most Recently Used") file list in Excel 2007

Note: In Excel 2010-2013 it is no problem anymore to hide the MRU list, see my backstage page.

There is no built-in option to disable the MRU("Most Recently Used") file list in the Office button menu.
Try this example file that create a new tab on the Ribbon and hide/disable a few controls with RibbonX and with VBA code from Jim Rech disable the MRU list.

Part from the comments in the code from Jim:
Because there is apparently no way to disable the MRU list on the Office Menu directly we clear it by setting Application.RecentFiles.Maximum to 0. Since this action clears the MRU list in the registry we first backup its contents to the registry as well as number of files the user is showing in it. We use the registry in case VB variables are cleared.

Download the example file

 

Examples for a dropdown in the Ribbon

You can download a zip file with two basic examples below.

Download dropdown.zip