Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Load different RibbonX when opening file in Excel 2007 or 2010/2016

If you want to insert RibbonX into a Excel 2007-2016 workbook to change the Ribbon, then I suggest you download and install the free Custom UI Editor to make this a lot easier : http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2006/05/26/customuieditor.aspx

Note: If the link is broken you can also download the Custom UI Editor from my site.

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

Custom UI Editor

The Custum UI 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-2016 and/or a customUI14.xml file that only loads when you open the Excel workbook in Excel 2010-2016. What, files that are inside a Excel file?.
Excel 2007-2016 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 Custom UI 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 2010-2016 both use the same file named customUI14.xml

When you not work with things that are added in Excel 2010-2016 (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-2016. But if you open the Excel file in Excel 2010-2016 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-2016 then you must insert RibbonX in both the xml files with the Custum UI Editor.

When you open a Excel file in the Custom UI 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.

Note: the button caption "Office 2010 Custom UI Part" is no longer correct since Office 2013 has been released. It should now be named "Office 2010-2016 Custom UI Part" but I do not think they will update the editor for this.

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-2016.

1: Open a new workbook and save it as Book1.xlsm
2: Close the workbook
3: Open Book1.xlsm in the Custom UI Editor
4: Right click on the File name in the Custom UI 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-2016

Tip: I suggest that you paste the RibbonX first in Notepad and then copy/paste it in the UI Editor, if you copy it directly it will not indent and is diffecult to read in the UI Editor.

<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 Custom UI 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-2016

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-2016.

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