VBA - Ribbon customization - When adding an add-on

Question

I did a program that I wished to distribute to my co-workers, they are not VBA oriented. So I wish to distribute it easily.

I created an Add-in, when install the add-in needs to create a custom Ribbon. I tried my best, but I cannot find easy to understand documentation.

My code is as follow :

Private Sub Workbook_AddinInstall()

On Error Resume Next 'Just in case

    Application.CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete

    Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add
        With cControl
            .Caption = "Open SCF workbook"
            .Style = msoButtonIconAndCaptionBelow
            .OnAction = "OpenTheCorrectFile"
            .FaceId = 7720
            .DescriptionText = "Open the SCF workbook"
        End With
               
    Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add
        With cControl
            .Caption = "Are they onboard"
            .Style = msoButtonIconAndCaptionBelow
            .FaceId = 5817
            .OnAction = "Check_Suppliers_Already_On_Board"
            .DescriptionText = "Check if suppliers have already been on boarded"
        End With
End Sub

Now if you use my code you will notice that:

Buttons are small
Icons are small
No description when hoovering them
No special name for the new ribbon, it is call Add-ins
No special name for this group, it is call "Menu Commands"

Does anyone know how to solve this. Whenever I am looking for those answer people are using special application to create the ribbons, I do not wish to do it. I am also a beginner in VBA, so everything that is easily digestible is welcome.

Thank you very much.

There's a TooltipText property you can use to set the hovering description (list of available properties: https://learn.microsoft.com/en-us/office/vba/api/overview/library-reference/commandbarbutton-members-office) — Ivan, Jan 28 '21 at 20:59
There are Width and Height properties as well but I coudn't make them work (button size didn't change). Apparently, better customizations now require XML, Custom UI Editor for Microsoft Office, or something like that. I think it won't be possible using just VBA. — Ivan, Jan 28 '21 at 21:56
There's a good example of how to do it here, without any applications, but you'd have to manually edit the customUI.xml file and the xlsm file structure (renaming to .zip, and editing/adding some files, and renaming again to .xlsm): https://learn.microsoft.com/en-us/office/vba/library-reference/concepts/customize-the-office-fluent-ribbon-by-using-an-open-xml-formats-file — Ivan, Jan 28 '21 at 22:20
Thank you very much Ivan. I will try what you advise me here. And thanks for the tooltip property. — VI55, Jan 29 '21 at 12:54

score 1 · Accepted Answer · answered Jan 28 '21 at 23:44

Here's an example of how you can do it without using any external application, just a text editor. But you will have to be careful to write XML code properly, or the excel file may not open correctly (make a backup copy).

You will create a folder named "customUI" and, inside that folder, a file named "customUI.xml"

You will write all your Ribbon details inside "customUI.xml". I wrote this example, covering the issues you listed: Big buttons and icons (Images list in https://bert-toolkit.com/imagemso-list.html) / Tooltip description (screentip and supertip) / Ribbon name (tab label) / Group names (group labels)

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">  
<ribbon>
  <tabs> 
    <tab id="CustomTab" label="Add-ins"> 
      <group id="Group1" label="Menu Commands"> 
        <button id="Button1" label="Happy" size="large" screentip="Test1" supertip="Test1 description" imageMso="HappyFace" onAction="Macro1" /> 
        <button id="Button2" label="Save" size="large"  imageMso="FileSave" onAction="Macro2" /> 
        <button id="Button3" label="{a}" size="large" imageMso="FieldCodes" onAction="Macro3" /> 
      </group > 
      <group id="Group2" label="Other Commands"> 
        <button id="Button4" label="Reply" size="large" imageMso="Reply" onAction="Macro4" /> 
      </group > 
     </tab> 
  </tabs> 
</ribbon> 
</customUI>

Rename your .XLSM file to .ZIP, then you will be able to edit/add files. Add the customUI folder to the zip file.
From the ZIP file, extract the .rels file that is inside _rels folder, you will add this line between the last Relationship tag and the Relationships closing tag, and then put it back inside the ZIP file overwriting the .rels file there
```
 <Relationship Id="someID" Type="http://schemas.microsoft.com/office/2006/relationships/ui/extensibility" Target="customUI/customUI.xml" /> 
```
Rename the file back to .XLSM and open it, if everything is right, the new Ribbon will be there. It's important that the macros that will be called by the buttons have a control parameter, like this:
```
 Sub Macro3(ByVal control As IRibbonControl)
```

That's it, everything about the UI is in the customUI.xml file, there is no more adding buttons in VBA. If you have to change something in the UI, you will have to rename it to ZIP again, overwrite the customUI.xml file and rename to XLSM again. Here's the Ribbon I got in this example:

A reference for Ribbon Buttons properties: https://learn.microsoft.com/en-us/openspecs/office_standards/ms-customui/846e8fb6-07d3-460b-816b-bcfae841c95b — Ivan, Jan 28 '21 at 23:44
I don't know how to code in XML, I guess I will have to learn it! Thank you so much for your details answer, it is very helpful — VI55, Jan 29 '21 at 12:55
You're welcome! Just one more detail: I noticed when I was testing the XML example that duplicate IDs would cause errors, that's why I called the buttons "Button1", "Button2", etc. If you create a lot of groups and buttons, be sure to avoid duplicate IDs. — Ivan, Jan 29 '21 at 13:16

VBA - Ribbon customization - When adding an add-on

1 Answers1