CustomUI XML allows to create backstage menu in excel. Backstage menu serves various purpose like hiding the menu, keeping your own customized menus or commands out of Ribbons etc. In this article we will see how to create backstage menu in Excel with the help of CustomUI XML.
Users are advised to visit my previous posts to understand complete architecture of CustomUI xml as:
- Customize or Create your own ribbon with controls in excel
- Context Menu or Right Click Popup creation in Excel
Step 1: Continue using same file “CustomAddin.xlsm” as I have used in my previous articles listed above. Open it in Microsoft Custom UI Editor and click on Insert menu >> Sample XML >> Custom OutSpace as shown below:
A sample backstage menu CustomUI xml gets inserted as shown below:
Step 2: Customize the XML, since backstage menu can be displayed in Column format where multiple menus can be put to gather under each column hence, you will encounter <firstColumn> tag. For our PoC we customized the XML and added three buttons to display Excel inbuilt dialogs on each click action:
<?xml version="1.0" encoding="utf-8"?> <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"> <backstage> <tab id="customVBAOVERALL" label="VBAOVERALL Options..."> <firstColumn> <taskGroup id="customTaskGroup" label="VBAOVERALL Task Group"> <category id="tgCategory1" label="First Category"> <task id="tOpenFile" label="File Open" image="File" onAction="FileOpen_Click"/> <task id="tConditionalFormatting" label="Conditional Formatting" image="folder" onAction="Conditional_Click"/> <task id="tAddinManager" label="Addin Manager" image="Setting" onAction="Addin_Click"/> </category> </taskGroup> </firstColumn> </tab> </backstage> </customUI>
Step 3: Add appropriate images for buttons that you wish to show on the UI and replace image parameter respectively. Click on Validate button to verify that your customization is valid. then Generate Callbacks for your customization.
Step 4: Copy call backs generated above and close Microsoft Custom UI Editor, Open “CustomAddin.xlsm” in Excel, Open VB editor and Insert a new module and paste the same. Now you can put your own code under each callbacks’ body as per your need as shown below:
Step 5: Close VB Editor and come back to Excel, Click on File menu and notice at the end a Custom menu has been added in the list. Click on “VBAOVERALL Options” menu and it will expand backstage commands as shown below:
Click on any command in Backstage menu and output should look like as shown below: