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

 

[Jeffrey Mahoney]

This is all I have for my first line. I don't how the extra stuff relates that you have in your first line

customUI On*load="Ribbonon*load" xmlns="http://schemas.microsoft.com/office/2009/07/customui"
(had to add the astrisks so it post correctly)

 

[senorVBA]

Does your RibbonOn*Load fire?

The extra stuff is there because i also modify the quick access toolbar.

I'm starting to wonder if this will only work with VB / C# and not VBA....but i haven't given up yet.

Thx for the feedback.

 

[Jeffrey Mahoney]

Yup, it works.

 

[jkpieterse]

If you have multiple workbooks containing CustomUI callbacks you must make sure each of them has a uniquely named 'on load' routine. Otherwise workbook B might call the DoStuff load event in Workbook A.

 

[senorVBA]

I know we're all very busy with our own workloads so thank you all very much for taking time to look at this!!!

I've removed all other add-ins. I'm now using some sample code i found on StackOverflow that they claimed to have gotten to fire. I adapted it so that my code creates the exact XML the OP was using. It still doesn't work. Here is all of my test code below. It is a subset of my production code so some of the comments aren't relevant to what is actually happening. It was just easiest for me to reuse my framework to re-create the XML from the StackOverflow example.

When i click the button, I get an "Argument not optional" error from the Button*Click sub. That sub is expecting the ribbon object passed on if the on*load fires. I even tried this on a fresh install of Win10/Office 2016 and still no go.

Someone please explain what is going wrong here. I'm at my wits end with this. This solution will be distributed to users as an Excel 2016 add-in with the file extension *.xlam.

Used '|' in place of the greater/less than symbols so it would post clearly. Also, asteriks used wherever the forum parser was replacing keywords.

Whenever uninstalling and installing the add-in, i overwrite the Excel.officeUI file with a clean, untouched one and verify it's clean before installing the add-in again.

TestRibbonRefresh.xlam:
All of this code is in the ThisWorkbook Object<group id="" grouptest""="" label="" 2010""="" insertbeforemso="" groupinsertlinks""=""></group>

Option Explicit
Const WhoCalled As String = "2010" 'this must equal the toolbar group label
Dim MyFSO As FileSystemObject


Function getButtonGrpXML() As String
    getButtonGrpXML = getButtonGrpXML & "           |group id=""GroupTest"" label=""2010"" insertBeforeMso=""GroupInsertLinks""|" & vbNewLine
    getButtonGrpXML = getButtonGrpXML & "               |button id=""ButtonTest"" getLabel=""GetLabel"" imageMso=""PivotDropAreas"" onAction=""Button*Click""/|" & vbNewLine
    getButtonGrpXML = getButtonGrpXML & "           |/group|" & vbNewLine
    
End Function

Private Sub Workbook_AddinInstall()
    Dim hFile As Long
    Dim Path As String, FileName As String, ribbonXML As String, user As String

    Dim CurrentXML As String, ribbonXMLHeader As String, ribbonXMLbuttons As String, ribbonXMLNew As String, ribbonXMLFooter As String
    Dim XPos1 As Long
    Dim XPos2 As Long

    Dim UIfile As File
    Dim txt As TextStream
    
    '------------------------------------------
    '------ prep path and file variables ------
    '------------------------------------------
    
    hFile = FreeFile
    user = Environ("Username")
    Path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"
    FileName = "Excel.officeUI"
    
    'prep for occassions when users profile is appended with .DOMAIN
    Set MyFSO = CreateObject("Scripting.FileSystemObject")
    If Not MyFSO.FolderExists(Path) Then
        user = Environ("Username") & ".DOMAIN"
        Path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"
    End If
    
    '-------------------------------------------------
    ' -------- Attach Excel.officeUI file to a object
    ' -------- Read contents into a variable.
    ' -------- Prep for editing.
    '-------------------------------------------------
    
    Set UIfile = MyFSO.GetFile(Path & FileName)
    Set txt = UIfile.OpenAsTextStream(ForReading)
    CurrentXML = txt.ReadAll
    Set txt = Nothing
    Set MyFSO = Nothing

    If InStr(CurrentXML, WhoCalled) = 0 Then 'the UI file already contains the buttons for this macro, do nothing
        If InStr(CurrentXML, "TabInsert") Then 'the UI file has been touched by one of my macros. Just add a group to existing RWP tab for this macro
            
        Else 'will always branch here for testing
    
                ribbonXML = "|customUI on*load=""on*LoadRibbon"" xmlns=""http://schemas.microsoft.com/office/2009/07/customui""|" & vbNewLine
                ribbonXML = ribbonXML & "  |ribbon startFromScratch=""false""|" & vbNewLine
                
                '-------------------------------------------------
                'Modify Quick Access toolbar with some useful toys
                '-------------------------------------------------
                'ribbonXML = ribbonXML & getQuickAccessGrpXML
                
                '----------------------
                ' Insert custom RWP tab
                '----------------------
                ribbonXML = ribbonXML & "    |tabs|" & vbNewLine
                ribbonXML = ribbonXML & "       |tab idQ=""TabInsert""|" & vbNewLine
                
                '---------------------------
                ' Button group for the macro
                '---------------------------
                ribbonXML = ribbonXML & getButtonGrpXML
                                
                '--------------------------
                ' END custom RWP tab insert
                '--------------------------
                ribbonXML = ribbonXML & "        |/tab|" & vbNewLine
                ribbonXML = ribbonXML & "     |/tabs|" & vbNewLine
                
                '--------------------------
                ' END ribbon & UI XML
                '--------------------------
                ribbonXML = ribbonXML & "  |/ribbon|" & vbNewLine
                ribbonXML = ribbonXML & "|/customUI|"
    
        End If
    
        '//////*
Debug.Print ribbonXML

        'ribbonXML = Replace(ribbonXML, """", "")
        
        Open UIfile For Output Access Write As hFile
        Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=hFile]#hFile[/URL] , ribbonXML
        Close hFile
        
    End If
    
    Set UIfile = Nothing
End Sub

-- The Excel.officeUI created by the code above --

|customUI on*Load="on*LoadRibbon" 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" imageMso="PivotDropAreas" onAction="Button*Click"/|
           |/group|
        |/tab|
     |/tabs|
  |/ribbon|
|/customUI|

-- And finally, the code in Module1 --

Option Explicit

Public Toggle As String
Public myRibbonUI As IRibbonUI

Public Sub on*LoadRibbon(ribbon As IRibbonUI)
    Set myRibbonUI = ribbon
    Debug.Print "Ribbon Reference Set"
    MsgBox "Ribbon Reference Set"
End Sub

' Ribbon callback : runs when ribbon button is clicked
Public Sub Button*Click(control As IRibbonControl)
    ' Invalidate the ribbon so that the label of the button toggles between "true" and "false"
    
    MsgBox ""
    '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

 

[Jeffrey Mahoney]

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?

Public rib As IRibbonUI

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then    Public Declare PtrSafe Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef destination As Any, ByRef source As Any, ByVal length As Long)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Public Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" (ByRef destination As Any, ByRef source As Any, ByVal length As Long)
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


'Callback for customUI.******
Sub Ribbon******(ribbon As IRibbonUI)
  Set rib = ribbon
  Range("ECPSaveRibbon") = ObjPtr(ribbon)
  rib.ActivateTab ("PORTAL")
End Sub




[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
  Function GetRibbon(ByVal lRibbonPointer As LongPtr) As Object
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
  Function GetRibbon(ByVal lRibbonPointer As Long) As Object
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    Dim objRibbon As Object
    CopyMemory objRibbon, lRibbonPointer, LenB(lRibbonPointer)
    Set GetRibbon = objRibbon
    Set objRibbon = Nothing
End Function


Sub RefreshRibbon()
  If rib Is Nothing Then
    Set rib = GetRibbon(Range("ECPSaveRibbon").Value)
  End If
  rib.Invalidate
  On Error GoTo NoRibbonMenu
  rib.ActivateTab ("PORTAL")
  On Error GoTo 0
  Exit Sub
NoRibbonMenu:
  MsgBox "An error occured while trying to reload the Portal Menu. Reload this workbook"
  On Error GoTo 0
  
End Sub

 

[jkpieterse]

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?

 

[RoryA]

The extra stuff is there because i also modify the quick access toolbar.

IMO, you shouldn't be doing that. The QAT is for the user to put things they want, not for developers. Your Ribbon customizations should be stored in the CustomUI parts in the add-ins, as Jan Karel said, rather than messing about overwriting the user's specific ribbon customization file. That's like you overwriting their personal macro workbook - not something you should be doing.

 

[jkpieterse]

I totally agree Rory.