Microsoft Excel

Ron de Bruin
Excel Automation

Microsoft MVP Program

Change the Ribbon in Excel 2007-2016

Things we must do and understand before we start

Note : If you are a Mac user follow the steps on this page : Change the Ribbon in Mac Excel 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.

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.

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

 

Open the Custom UI Editor

You see that there are 5 menu buttons on the toolbar menu in the UI editor.

Open : Open the Excel file that you want to edit (change the Ribbon for this file)
Save : Save the RibbonX in the file that you have open in the UI editor
            (not possible to save when the file is open in Excel)
Insert Icons : Insert your own icons in the file (icons will be saved in the file)
Validate : Validate your RibbonX so you are sure that there are no typos
Generate Callbacks : This will create macros(callbacks) for each onAction you have in
the RibbonX. Select all callbacks and copy them in a normal module in your workbook.

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.

Example for 2007-2016

In this example we add RibbonX to the file that create a button on the Home tab when you open your file in Excel 2007-2016. Because we not use something new for 2010-2016 we only have to add the RibbonX to the customUI.xml file ("Office 2007 Custom UI Part") .

1: Open a new workbook and save it as Book1.xlsm (Excel Macro-Enabled workbook)
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 or use the Insert menu
5: Choose "Office 2007 Custom UI Part" to create the customUI.xml file
6: Paste the RibbonX below in the right window
    Note: If there is no customUI14.xml file it will load the customUI.xml also in Excel 2010-2016.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>

      <tab idMso="TabHome" >
        <group id="customGroup1" label="My Group" insertAfterMso="GroupEditingExcel">

          <button id="customButton1" label="Click Me" size="large" 
		onAction="Macro1" imageMso="HappyFace" />
        </group>
      </tab>

    </tabs>
  </ribbon>
</customUI>

7: Save your changes in the Custom UI Editor (click on the Save button)
8: Close the Editor
9: Open the File in Excel
10: Alt F11
11: Insert Module
12: Copy the macro below in the Module

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

13: Click on the Save button in the VBA editor
14: use Alt q to close the VBA editor
15: Test the workbook in 2007 and/or in 2010-2016

Note: For example workbooks visit this page : Ribbon Examples files and Tips

 

Example to load different RibbonX in 2007 and in 2010-2016

For a example to Load different RibbonX when opening the same Excel file in Excel 2007 or in 2010-2016 visit: Load different RibbonX when opening file in Excel 2007 or 2010/2016

 

Ribbon Examples files and Tips

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