CustomUI on Load event is not firing

[senorVBA]

Hello all!

I have series of add-ins that insert their own buttons into my custom tab on the Excel 2016 ribbon. The code runs from Private Sub Workbook_AddinInstall() in ThisWorkbook of each add-in. No problems there. I've created an add-in manager form where users can select what add-ins they want to add or remove. No problems there either.

But when an add-in is uninstalled, the buttons remain behind in the custom tab until Excel is closed and re-opened. I want the buttons to disappear immediately upon uninstall of the add-in. I've found code i think may force a refresh of the ribbon. But the author suggests calling the sub from the ribbon's on Load event handler. I've read through MSDN and a number of other google hits to come up with the following but it wont fire.

The Excel.officeUI XML contains the following: (forum removes "on load" if written without space)

mso:customUI on Load="DoStuff" xmlns:x1="http://schemas.microsoft.com/office/2009/07/customui/macro" xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui"
<mso:customui ******="Ribbon******" xmlns:x1="http://schemas.microsoft.com/office/2009/07/customui/macro" xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui"><mso:customui on="" load="DoStuff" xmlns:x1="http://schemas.microsoft.com/office/2009/07/customui/macro" xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui"><mso:ribbon>
Tried VBA in both a standard module and ThisWorkbook

  <mso:customui ******="Ribbon******" xmlns:x1="http://schemas.microsoft.com/office/2009/07/customui/macro" xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui"><mso:customui ******="Ribbon******" xmlns:x1="http://schemas.microsoft.com/office/2009/07/customui/macro" xmlns:mso="http://schemas.microsoft.com/office/2009/07/customui">Public Sub DoStuff(ribbon As IRibbonUI)
    Debug.Print "ribbon loaded"
    MsgBox "ribbon loaded"
 End Sub<mso:ribbon>
<mso:ribbon><mso:tabs><mso:tab id="mso_rwp" label="RWP" insertbeforeq="mso:TabInsert"><mso:group id="mso_runpersh" label="PERSHING"><mso:button idq="x1:RunDC" label="Commission" imagemso="PivotDropAreas" onaction="PDC_Begin" size="large" supertip="Format any Daily Commission report" visible="true"><mso:button idq="x1:RunAH" label="Aged Haircut" imagemso="OutlineWeightGallery" onaction="PAH_Begin" size="large" supertip="Format the Aged Haircut report" visible="true"><mso:button idq="x1:RunML" label="Money Line" imagemso="ThemeColorsGallery" onaction="PML_Begin" size="large" supertip="Format the Money Line report" visible="true"><mso:button idq="x1:RunPCPS" label="Payout Summary" imagemso="GroupSmartArtQuickStyles" onaction="PCPS_Begin" size="large" supertip="Format the Commission and Payout Summary report" visible="true"><mso:button idq="x1:RunPDK" label="DK Interest" imagemso="AppointmentColorDialog" onaction="PDK_Begin" size="large" supertip="Format any DK Interest report" visible="true"><mso:button idq="x1:RunPSPR" label="Short Rebate" imagemso="ViewBackToColorView" onaction="PSPR_Begin" size="large" supertip="Format the Short Position Rebate report" visible="true"><mso:button idq="x1:RunPDKF" label="DK Int. Fails" imagemso="DiagramStylesClassic" onaction="PDKF_Begin" size="large" supertip="Format the Monthly DK Interest Fails report" visible="true"><mso:button idq="x1:RunPI" label="Interest" imagemso="AnimationTransitionGallery" onaction="PI_Begin" size="large" supertip="Format the Pershing Monthly Interest report" visible="true"></mso:button></mso:button></mso:button></mso:button></mso:button></mso:button></mso:button></mso:button></mso:group></mso:tab></mso:tabs></mso:ribbon></mso:ribbon></mso:customui>

</mso:customui></mso:ribbon></mso:customui>
</mso:customui>To be honest, i don't really understand this on a conceptual level. All of my hits on google lead to a OP figuring it out on their own and not posting the solution. I hope this can become that google hit

 

[RoryA]

We must be right then.

 

[senorVBA]

I don't know if this will help. I got this code a long time ago. I can't remember the source.

If for any reason your VBA project is reset, the variable you store your Ribbon value in will be lost. My Ribbon****** saves it to a cell in a safe place, and can be recalled.

Check if your Ribbon variable is valid
if myRibbonUI is nothing then...

I think I'm reaching on this, but I can't see what's going on. Is your "on*LoadRibbon(ribbon As IRibbonUI)" actually running at startup?

Therein lies the issue...on*LoadRibbon is NOT running at startup. It refuses to fire and won't even throw an error. The custom toolbar loads and functions as designed but on*Load is ignored. I should get MsgBox on load but nothing. I will try to store the value in the hidden sheet but I'm not optimistic. Thanks for the suggestion.

Why are you creating the ribbonX xml in VBA? Your code does not show where the ribbon xml file is used. Why not use the customUI editor?

Because i create several different macros each stored in individual xlam files. The customUI editor only works to customize on each individual xlam at a time, as far as i can tell. When i did it that way, the macros were not be "aware" of each other's controls and wiped each other out when installed. Only the controls from the final add-in would remain in the toolbar. With my method, i can check for existing buttons and insert while preserving existing controls from previously installed add-ins. Furthermore, when any of my add-ins are uninstalled, it takes it's controls with it and leaves the others in place. I don't think there's any way at all to even begin to do that with the editor.

Hence this post and my current struggle! GRRRR. But I can post the production version if you'd like to play with it.

We must be right then.

Indeed you are! That's why i made it a function i can quickly comment out. But my users aren't at all tech savvy so 98.384% were ok with it. The 2 that weren't, i use the environment variable to create custom QATs for each of them. Yes, yes it's OVERKILL but fun! lol

Now let's make the on*load magic work for me I can release without and let them deal with a restart of excel but that's so ugly, ya know.

 

[RoryA]

AFAIK, on Load doesn't apply to the officeui file since that's not how it's supposed to be used.

If you need to share tabs between add-ins you need to look at the idQ attribute.

 

[jkpieterse]

You should buy the RibbonX book (Ken Puls is one of the authors), it should help you to have one ribbon tab for multiple add-ins.

 

[senorVBA]

AFAIK, on Load doesn't apply to the officeui file since that's not how it's supposed to be used.

If you need to share tabs between add-ins you need to look at the idQ attribute.

You just confirmed my earlier suspicions and broke my heart. But I will we keep trying anyway. The dent in the wall is not quite the shape of my entire face yet.

You should buy the RibbonX book (Ken Puls is one of the authors), it should help you to have one ribbon tab for multiple add-ins.

Bought! Will be reading it on the train ride home. Thanks!