VBA to create XML Ribbon file

[Jake Blackmore]

Hi I am very new to XML and I believe I am missing some fundamental knowledge with the way Excel loads them.

I've created an XML file in VBA using the following script on an Open event. The script seems ok as the tab is gerenated in the ribbon but that's about it. It doesn't seem to be in anyway active or actually loaded because it doesn't even attempt to call the macro.

Option Explicit
Public g_blnStockDownLoad As Boolean
Public g_rxIRibbonUI As IRibbonUI

Public Sub OpenTableManager()
    Dim lngFile         As Long 'next file number available for use by the FileOpen function
    Dim strPath         As String
    Dim strFileName     As String
    Dim strRibbonXML    As String
    Dim strUser         As String
    
    lngFile = FreeFile
    strUser = Environ("Username")
    strPath = "C:\Users\" & strUser & "\AppData\Local\Microsoft\Office\"
    strFileName = "Excel.officeUI"
    
    'XML Coding notes each line / attribute needs a space at the end. Probably can't use .xlsb's as Binary files don't use XML
                   strRibbonXML = "<customUI xmlns=''http://schemas.microsoft.com/office/2009/07/customui'' "
    strRibbonXML = strRibbonXML & "on Load=''rxIRibbonUI_on Load''> "
    strRibbonXML = strRibbonXML & "<ribbon> "
    strRibbonXML = strRibbonXML & "<tabs> "
    strRibbonXML = strRibbonXML & "<tab id=''rxTrading'' label=''Trading''> "
    strRibbonXML = strRibbonXML & "<group id=''rxDownloadStockData'' label=''Download Stock Data''> "
    
    strRibbonXML = strRibbonXML & "<toggleButton id=''rxEnableDisableDowwnload'' "
    strRibbonXML = strRibbonXML & "onAction=''rxToogleDownload'' "
    strRibbonXML = strRibbonXML & "getLabel=''rxEnableDisableDowwnload_Label'' "
    strRibbonXML = strRibbonXML & "size=''large''/>"
    
    strRibbonXML = strRibbonXML & "<button id=''rxDownloadData_name'' "
    strRibbonXML = strRibbonXML & "label=''Download Names'' "
    strRibbonXML = strRibbonXML & "onAction=''rxDataStock_Name'' "
    strRibbonXML = strRibbonXML & "getEnabled=''rxDataStock_Enable''/> "
    
    strRibbonXML = strRibbonXML & "</group> "
    strRibbonXML = strRibbonXML & "</tab> "
    strRibbonXML = strRibbonXML & "</tabs> "
    strRibbonXML = strRibbonXML & "</ribbon> "
    strRibbonXML = strRibbonXML & "</customUI>"
    strRibbonXML = Replace(strRibbonXML, "''", """")
Debug.Print strRibbonXML

    Open strPath & strFileName For Append Access Write As lngFile
    Print #lngFile, strRibbonXML
    Close lngFile

End Sub

Public Sub CloseTableManager()
    Dim lngFile         As Long 'next file number available for use by the FileOpen function
    Dim strPath         As String
    Dim strFileName     As String
    Dim strRibbonXML    As String
    Dim strUser         As String
    
    lngFile = FreeFile
    strUser = Environ("Username")
    strPath = "C:\Users\" & strUser & "\AppData\Local\Microsoft\Office\"
    strFileName = "Excel.officeUI"
    strRibbonXML = "<mso:customUI           xmlns:mso=""http://schemas.microsoft.com/office/2009/07/customui"">" & _
    "<mso:ribbon></mso:ribbon></mso:customUI>"
    
    Open strPath & strFileName For Output Access Write As lngFile
    Print #lngFile, strRibbonXML
    Close lngFile
End Sub
 
 'Callback for rxEnableDisableDowwnload getLabel
Sub rxEnableDisableDowwnload_Label(control As IRibbonControl, ByRef returnedVal)
    If g_blnStockDownLoad Then
        returnedVal = "Disable Download"
    Else
        returnedVal = "Enable Download"
    End If
End Sub
 
Public Sub rxDataStock_Name() '(control As IRibbonControl)
    MsgBox "Do Download"
End Sub
 
Private Sub rxIRibbonUI_******(ribbon As IRibbonUI)
    Set g_rxIRibbonUI = ribbon
End Sub

 'Callback for rxEnableDisableDowwnload onAction
Sub rxToogleDownload(control As IRibbonControl, pressed As Boolean)
    g_blnStockDownLoad = pressed
    g_rxIRibbonUI.Invalidate
End Sub
 
Sub rxDataStock_Enable(control As IRibbonControl, ByRef returnedVal)
    If g_blnStockDownLoad Then
        returnedVal = True
    Else
        returnedVal = False
    End If
End Sub

(You will need to delete the space from "on Load", it's used twice on one line)

If I was to use the "Custom UI Editor For Microsoft Office" and place the same XML into the file (with the respective VBA code above for the features) then it works as it should.

Therefore I believe that perhaps I am not loading it or calling it properly with the VBA. Perhaps it's not really generating the correct file or in the correct place but because I'm very new to this I'll be honest in that I haven't got a clue.

Any support anyone can offer me would be really appreciated as I would like to use this functionality to create Ribbon buttons (that actually work) on the fly in VBA.

Jake

 

[RickXL]

If you want to install the Add-In using VBA you could start here: Installing An Add-In

 

[RickXL]

Jaafar,

Yes, the custom tab loads and the two buttons. The one with a fixed label shows the label and the one with the getLabel callback does not.

In fact, I can see no evidence of any callback working at all - even the "on Load" one. I did try fully qualifying the macro name but even that did not make any difference. It looked to me as if the "static" part worked but nothing callback-related.

I think the OP will have a problem with overwriting any existing user customizations to QAT and Ribbon. Also, it occurred to me that the intended functionality did not need any callbacks to work so maybe it has not been implemented. How would MS test it anyway? They would need to try something similar to the OP.

 

[Jake Blackmore]

Yes for Excel 2007 it would need "2006/01" apologies as I failed to say I'm running 2013.
Jake

Did you guys get the custom tab to load ? I can't seem to have the tab loaded
I deleted the spaces as directed and changed the strPath to my desktop for easier access and because I use excel 2007, I also changed the schemas line to : http://schemas.microsoft.com/office/2006/01/customui