onLoad ribbon callback fails (not firing/working) from Excel VBA .xlam

Question

I've created a simple Excel add-in to demonstrate the problem whereby the ribbon onLoad event isn't firing when the project is saved and automatically loaded as a .xlam but does work when opened as a .xlsm. I've checked it for Excel 2007 (with the corresponding xmlns), 2010 (x32) and 2013 (x32) and it fails in all cases when loaded as a .xlam

The sample has a single button and when clicked, should cause a flag to be toggled and then invalidates the ribbon which in turn toggles the label of the button via the GetLabel callback.

It works when opened as an .xlsm file but not when loaded automatically from the user XLSTART folder (no Windows Registry changes have been made) as a .xlam add-in. The problem seems to be that the onLoad event isn't firing from the .xlam version and hence the onLoadRibbon procudure doesn't run and there's no ribbon object to invalidate.

<customUI onLoad="onLoadRibbon" xmlns="http://schemas.microsoft.com/office/2009/07/customui">
    <ribbon startFromScratch="false">
        <tabs>
            <tab idQ="TabInsert">
                <group id="GroupTest" label="2010" insertBeforeMso="GroupInsertLinks">
                    <button id="ButtonTest"
                        getLabel="GetLabel"
                        onAction="ButtonClick"/>
                </group>
            </tab>
        </tabs>
    </ribbon>
</customUI>

And here's the corresponding add-in VBA code:

Option Explicit

Public Toggle As String
Public myRibbonUI As IRibbonUI

' Ribbon callback : runs when ribbon is loaded
Public Sub onLoadRibbon(ribbon As IRibbonUI)
    ' Get a reference to the ribbon
    Set myRibbonUI = ribbon
    Debug.Print "Ribbon Reference Set"
    MsgBox "Ribbon Reference Set"
End Sub

' Ribbon callback : runs when ribbon button is clicked
Public Sub ButtonClick(control As IRibbonControl)
    ' Invalidate the ribbon so that the label of the button toggles between "true" and "false"
    myRibbonUI.Invalidate
    Debug.Print "Ribbon Invalidated"
End Sub

' Ribbon callback : runs when ribbon is invalidated
Public Sub GetLabel(control As IRibbonControl, ByRef label)
    ' Toggle the label for the button to indicate that the callback has worked
    Toggle = IIf(Toggle = "State 1", "State 2", "State 1")
    label = Toggle
    Debug.Print "Ribbon Button Label Toggled"
End Sub

Why isn't this working in the .xlam?

Answer 1

I was able to successfully create and run the Add-In per your specifications.

First, I created the *.xlsm using your customUI.xml as above. NOTE: I'm on Excel 2007, so I used the other xlmns as you indicated, specifically http://schemas.microsoft.com/office/2006/01/customui. I added the ribbon-handling code next, then reopened the workbook to verify the ribbon functionality. I immediately received a messagebox "Ribbon Reference Set" (I have macros enabled by default). I verified that the "Insert" tab has your extra button and that it toggles.

Second, I saved the xlsm as an Excel Add-In *.xlam. I manually opened the xlam file by double clicking and all functionality appeared as in the first test above.

Third, I installed the Add-In through the Excel File Menu ---> Options ---> Add-Ins ---> Manage Excel Add-Ins ---> Go. I opened an unrelated Excel workbook and verified that the Add-In loaded the same as the tests before. I uninstalled the Add-In using the same menu.

Fourth, I navigated to C:\Program Files(x86)\ and verified the location of the Excel.exe executable, then found the XLSTART folder. I placed the xlam into the folder and opened the unrelated Excel workbook and was able to verify that the Add-In functioned as before.

I know that the above doesn't directly answer your question, but test those cases and let's compare the outcomes to find out where it might be breaking down.

EDIT:

In response to your comment, when I manually copy the *.xlam into the %APPDATA%\Microsoft\Excel\XLSTART folder, it works as expected. On my Windows 7 machine, the path evaluates to C:\Users\username\AppData\Roaming\Microsoft\Excel\XLSTART. As you noted, I verified in the Excel options that this is a default trusted location.