How to Customize the Excel 2016 Ribbon with XML

Excel Sales Forecasting For Dummies

In some situations, you may want to modify the Excel Ribbon automatically when a workbook or add-in is opened. Doing so makes it easy for the user to access your macro. It also eliminates the need for the user to modify the Ribbon manually by using the Excel Options dialog box.

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:

Create a new Excel workbook.
Save the workbook, and name it ribbon modification.xlsm.
Close the workbook.
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.”
In the Custom UI Editor, choose File → Open and find the workbook you saved in Step 2.
Choose Insert → Office 2007 Custom UI Part.

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

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>

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.
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.
Go back to the customUI.xml module and choose File → Save (or click the Save icon on the toolbar).
Close the file by choosing the File → Close command.
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.
Press Alt+F11 to activate the VBE.
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
```
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.

In the Custom UI Editor, when you choose Insert → Office 2007 Custom UI Part, you insert a UI part for Excel 2007. The Custom UI Editor also has an option to insert a UI part for Excel 2010 (the software I used has not been updated for Office 2013 or Office 2016). For maximum compatibility, use the Excel 2007 Custom UI Part.

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.