CODEWITHSUNDEEP
vbaexcel-addins
VI55
VI55

Reputation: 69

VBA - Ribbon customization - When adding an add-on

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:

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.

Upvotes: 3

Views: 2861

Answers (1)

Ivan
Ivan

Reputation: 446

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).

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

  2. 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>

  1. Rename your .XLSM file to .ZIP, then you will be able to edit/add files. Add the customUI folder to the zip file.

  2. 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" />

  3. 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:

Ribbon Menu Test

Upvotes: 1

Related Questions