Microsoft Excel
Back to jkp-ads.com

Ron de Bruin
Excel Automation

Microsoft MVP Program

Load different RibbonX when opening file in Excel 2007 or 2010 and up

Important message to visitors of this page

Ron de Bruin decided to remove all Windows Excel content from his website for personal reasons. If you want to know why, head over to rondebruin.nl.

Luckily, Ron was kind enough to allow me to publish all of his Excel content here.

Most of these pages are slightly outdated and may contain links that don 't work. Please inform me if you find such an error and I'll try to fix it.

Kind regards

Jan Karel Pieterse


If you want to insert RibbonX into a Excel 2007 and up workbook to change the Ribbon, then I suggest you download and install the free Office RibbonX Editor created by Fernando Andreu to make this a lot easier : https://github.com/fernandreu/office-ribbonx-editor/releases/latest

Before Fernando Andreu released this tool we used a tool named Custom UI Editor created by Trang Luu, you can download it here if you want to test this very old tool, but I suggest that you use the new tool that Fernando Andreu created that is up to date.

Note: It is not possible to insert RibbonX into Excel 97-2003(xls) files.

Office RibbonX Editor

The Office RibbonX Editor gives you an option to insert a customUI.xml file in your Excel workbook that loads when you open the file in Excel 2007 and up and/or a customUI14.xml file that only loads when you open the Excel workbook in Excel 2010 and up. What, files that are inside a Excel file?.
Excel 2007 and up files are really zip files so if you change the extension to zip and open the file in your zip program you see that there are a few folders and a file inside the zip. Thanks to the Office RibbonX Editor we not have to do all this to add or edit the customUI.xml or CustomUI14.xml file inside your Excel file.

Note: You see that Excel versions 2010 and up use the same xml file named customUI14.xml

When you not work with things that are added in Excel 2010 and up (like Backstage View for example), then you can only use the Office 2007 Custom UI Part option to add your RibbonX, it will load the RibbonX from this file when you open the Excel file in Excel 2007 and up. But if you open the Excel file in Excel 2010 and up and there is also a customUI14.xml file it will load only this file.

If you want to insert (for example) a button in the Office Button menu when you open your file in Excel 2007 and a button in Backstage View when you open the workbook in Excel 2010 and up then you must insert RibbonX in both the xml files with the Office RibbonX Editor.

When you open a Excel file in the Office RibbonX Editor you can right click on the file name and choose :
Office 2007 Custom UI Part Or use Office 2010+ Custom UI Part
Or you can use the Insert menu to choose one of the two options.

 

It creates the customUI14.xml file if you choose Office 2010+ Custom UI Part and the customUI.xml file if you choose Office 2007 Custom UI Part. After you choose one option or both, you can enter or paste your RibbonX in the right window.

In the screenshot shown below, you see that I used both options:

 

 

Example

This example add RibbonX that creates a button in the Office Button menu when you open your file in Excel 2007 and a button in Backstage View when you open the workbook in Excel 2010 and up.

1: Open a new workbook and save it as Book1.xlsm
2: Close the workbook
3: Open Book1.xlsm in the Office RibbonX Editor
4: Right click on the File name in the Office RibbonX Editor
5: Choose "Office 2010+ Custom UI Part"
6: Choose "Office 2007 Custom UI Part"
7: Paste the RibbonX below in the customUI14.xml file for Excel 2010 and up

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <backstage>
        <button id="MyCustomButton1" label="My Macro"
		imageMso="HappyFace" isDefinitive="true" onAction="Macro1"/>
    </backstage>
</customUI>

8: Paste the RibbonX below in the customUI.xml file for Excel 2007

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <officeMenu>
            <button id="MyCustomButton1" label="My Macro" 
		imageMso="HappyFace" onAction="Macro1" />
        </officeMenu>
    </ribbon>
</customUI>

9: Save your changes in the Office RibbonX Editor (click on the Save button)
10: Close the Editor
11: Open the File in Excel
12: Alt F11
13: Insert Module
14: Copy the macro below in the Module

Sub Macro1(control As IRibbonControl)
    MsgBox "Hi There"
End Sub

15: Click on the Save button in the VBA editor
16: use Alt q to close the VBA editor
17: Test the workbook in 2007 and in 2010 and up

If you do it correctly, you see a button in the Office Button menu in Excel 2007 and a button in Backstage View in Excel 2010 and up.

Important:
If you have also other xml that change the Ribbon add the xml for Backstage then above the last line </customUI>
It will not work if you add the backstage xml above the xml that change the Ribbon.

 

Ribbon Examples files and Tips

Visit this page for tips and more examples : Ribbon Examples files and Tips