How to Customize the Excel 2016 Ribbon with XML

You can make automatic changes to the Ribbon with Excel 2007 and later versions, but it’s not a simple task. Modifying the Ribbon involves writing XML code in a text editor, copying that XML file into the workbook file, editing a bunch of XML files (which also are stashed away inside the Excel file, which in reality is nothing more than a zipped container of individual files), and then writing VBA procedures to handle the clicking of the controls you put in the XML file.

Fortunately, software is available to assist you with customizing the Ribbon — but you still need to be on familiar terms with XML.

Follow these steps exactly:

1. Create a new Excel workbook.

2. Save the workbook, and name it ribbon modification.xlsm.

3. Close the workbook.

4. Launch the Custom UI Editor for Microsoft Office.

   If you don’t have this software, you need to find it and install it. Refer to the nearby sidebar “Get the software.”

5. In the Custom UI Editor, choose File  →  Open and find the workbook you saved in Step 2.

6. Choose Insert  →  Office 2007 Custom UI Part.

   Choose this command even if you’re using Excel 2010, Excel 2013, or Excel 2016.

7. Type the following code in the code panel (named customUI.xml) displayed in the Custom UI Editor:

   

   RibbonX code displayed in the Custom UI Editor.

   <customUI xmlns=‘http://schemas.microsoft.com/office/2006/01/customui’>
   <ribbon>
   <tabs>
   <tab idMso=‘TabHome’>
    <group id’Group1’ label=‘Excel VBA For Dummies’>
     <button id=‘Button1’
       label=‘Click Me’
       size=‘large’
       onAction=‘ShowMessage’
       imageMso=‘FileStartWorkflow’ />
     </group>
   </tab>
   </tabs>
   </ribbon>
   </customUI>

8. Click the Validate button on the toolbar.

   If the code has any syntax errors, you get a message that describes the problem. If any errors are identified, you must correct them.

9. Click the Generate Callback button.

   The Custom UI Editor creates a VBA Sub procedure that is executed when the button is clicked. This procedure is not actually inserted into the workbook, so you need to copy it for later use (or memorize it, if you have a good memory).

   

   The VBA callback procedure that is executed by clicking the Ribbon button.

10. Go back to the customUI.xml module and choose File  →  Save (or click the Save icon on the toolbar).

11. Close the file by choosing the File  →  Close command.

12. Open the workbook in Excel, and click the Home tab.

    You should see the new Ribbon group and Ribbon button. But it doesn’t work yet.

13. Press Alt+F11 to activate the VBE.

14. Insert a new VBA module; paste (or type) the callback procedure that was generated in Step 9; and add a MsgBox statement, so you’ll know whether the procedure is actually being executed.

    The procedure is

    Sub ShowMessage(control As IRibbonControl)
      MsgBox "Congrats. You found the new ribbon command."
    End Sub

15. Press Alt+F11 to jump back to Excel, and click the new button on the Ribbon.

    If all goes well, you see the MsgBox.

    

    Proof that adding a new Ribbon command using XML is actually possible.

You probably realize that modifying the Ribbon using XML is not exactly intuitive. Even with a good tool to help (such as the Custom UI Editor), you still need to understand XML. If that sounds appealing to you, search the web or find a book devoted exclusively to customizing the Ribbon interface in Microsoft Office.