How to create an addin with a toolbar in it

Question

I am struggling to find a solution to my problem.

I designed several UserForms and wrote many functions in a Workbook, and I added a new toolbar with buttons that call those functions.

Now what I want is to save this Workbook as an add-in so that when I add the add-in on another PC the toolbars appear.

But the toolbars does not appear. I could save the Workbook as an add-in with all the functions and the UserForms, but I couldn't get the toolbar.

Any suggestions?

Answer 1

By no means a solution, but maybe the following provides a starting point:

From what you say I assume you managed

• to activate your *.xlam add-in on another pc
• you could access all it's vba-code and userforms
• you could not access it's CustomUI-ribbon

Check if your final *.xlam still has the toolbar the toolbar in it when you deploy it:

• make a backup of your ready-to-deploy add-in file (*.xlam).
• open the file, either by changing it's file extension to .zip. (There is no need to rename it if you're using a file archiver like 7-zip)
• the contents of the file \customUI\customUI.xml should roughly resemble the following.

Example XML:

<customUI
xmlns="http://schemas.microsoft.com/office/2006/01/customui"
xmlns:shared="sharedNamespace"
onLoad="onLoadRibbonDB"
>
 <ribbon>
    <tabs>
        <tab idQ="shared:tabControls" label="AddIn_Beispiele" >

            <group id="grpA" label="Buttons">
                <button id="buttonA" label="Button_large" image="bunny2" onAction="buttonA_Click" size="large"/>
            </group>    

        </tab>
    </tabs>
 </ribbon>
</customUI>

Note that Excel won't show the ribbon if there are even minor errors in it. For testing, exclude anything that could result in an error, then re-introduce your ribbon controls step by step. If found the following the most common:

• id's are not distinct (e.g. used in other ribbons / add-ins)
• custom icons are not formatted correctly
• custom icons are not referenced correctly

Off-topic: if you plan to deploy the file across numerous PCs, a simple macro like the following could work for you:

Sub InstallAddIn()

    On Error GoTo skpError

    Dim path as String
    path = "L:\SQL_AddIn\SQL_AddIn_V1.0.xlam"
    name = "SQL_AddIn_V1.0"

    ' Copy the file
    With New FileSystemObject
        .CopyFile _
            path _
            , "C:\Users\" & Environ("USERNAME") & "\AppData\Roaming\Microsoft\AddIns\"
    End With

    ' Add-In Aktivieren
    AddIns(name).Installed = True
    MsgBox ws1.Cells(1, 2).Value & " installiert.", vbInformation
    Exit Sub

skpError:
    MsgBox "Fehler #" & Err & vbNewLine & Error

End Sub