CustomUI on Load event is not firing

senorVBA

New Member
Joined
Jan 26, 2017
Messages
15
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
Code:
  <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 :)
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top