Sep 30, 2014

We all know how to customise the Ribbon in Microsoft Excel by using the graphical user interface, but the problem with this way of customisation is that if you move the workbook to another environment, the tabs and buttons may not be there because it is a different Excel Application. Therefore, if you have assigned a button to run a macro in the workbook then you will no longer have that button. Moreover, if you save the workbook with another name, even though you have the button, it won’t work because it was assigned to run the macro in a workbook with the previous name, not the new one. So what’s the solution? Well, in these situations you need to customise the Ribbon programmatically so that no matter where and when it is opened, it would always have the intended tabs, buttons and functions. In other words the tab is attached to the Workbook, and not Excel Application. So let’s see how by following these steps;

Step 1. Create a blank Excel workbook and go to its Visual Basic environment.

Step 2. Double click on 'ThisWorkbook' and type the following in the work area:

Public Sub MyMessage (ByVal control As IRibbonControl)

      MsgBox "Hello World!"

End Sub

Of course, you may choose a better name for the sub. In this case, I have chosen MyMessage. The body of the sub could be any code, for example, you could call a macro name.

Step 3. Save the workbook on the Desktop as a Macro-Enabled Workbook and close it. Here, I call it MyWorkbook.xlsm.

Step 4. On the Desktop, create a folder called customUI. Observe the case.

Step 5. Open notepad and type the following in it. It is not difficult to understand the structure of this XML code. You can modify it to add more buttons, groups or tabs to it later on. Save it as customUI.xml within customUI folder and close notepad.

<?xml version="1.0" encoding="utf-8" ?>

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

  <ribbon>

    <tabs>

      <tab id="TabAddIns" label="my Tab">

        <group id="MyGroup" label="My Group">

          <button id="CustomButton" label="Message" size="large" imageMso="HappyFace" onAction="ThisWorkbook.MyMessage" />

        </group>

      </tab>

    </tabs>

  </ribbon>

</customUI>

Step 6. Rename MyWorkbook.xlsm to MyWorkbook.zip and confirm the change if asked.

Step 7. Open the zip file and extract the file .rels located to the folder _rels to the Desktop.

Step 8. Open .rels in notepad and add this line to the end just before the tag </Relationships>. Please note that the RelationshipId should be unique in this file and if "rId4" already exists, change it to something else.

<RelationshipId="rId4" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml"/>

Step 9. Save the changes and close the editor.

Step 10. Now replace the .rels in the zip file with the one you just modified. You might just drag it into your zip program to replace it.

Step 11. Drag the customUI folder into the zip. It should be in the same level as _rels and xl and docProps.

Step 12. Close your zip program if you have to and rename the MyWorkbook.zip back to MyWorkbook.xlsm

Step 13. Open MyWorkbook.xlsm and if you have done everything correctly, you should see an extra tab called “My Tab” with a smiley face button in it. When you click the button, the message in the macro appears saying "Hello World!" If you rename the file or send it to anyone else, upon opening the file, the tab will still be there and the button will function correctly.