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]

Hi,

I am not an expert in XML either but I have had a good look round and I am beginning to think that what you are trying to do may not be possible.

As far as I know, the Excel.CustomUI file is meant for Quick Access Toolbar and Ribbon customizations as made by a user. I suspect that the full functionality of Ribbon customization may not be available. I can't even get the on*Load macro to run (yes I did correct the name and no I don't know why it changes either!)

It may be that you cannot run callbacks from that file. I can get the buttons showing but there is no sign of either the on*Load macro or any callback macros running.


Have you thought of creating an Add-In instead? You can customize the ribbon in an Add-In and distribute that to other users. There are several sites which have details of how to do that. Here is one: Change the Ribbon in Excel 2007 - 2013

Sorry I can't be any more help.

Regards,

 

[Smitty]

See: Custom UI Editor Tool - OpenXML Developer - Blog - OpenXML Developer

This utility makes it a breeze.

HTH

 

[RickXL]

Yes, but not in the file the OP is trying to use.

That is why I suggested using an Add-In which does use the approved method and CustomUI would be appropriate.

 

[Smitty]

Yes, but not in the file the OP is trying to use.

That is why I suggested using an Add-In which does use the approved method and CustomUI would be appropriate.

Sorry, I didn't see that he already tried that approach (I'm on a crappy monitor in a remote office). But why try with VBA what you can easily do with the Editor?

 

[RickXL]

I can't speak for the OP but I guess he wanted a way to distribute the ribbon. If there was a simple file that could just be copied into a folder then that would be an easy solution. On the face of it using the Excel.CustomUI file looks as if it might work. However, I think it was intended for internal use only and may not support the full ribbon functionality.

If that is what the OP wants then I know the Add-in approach can have customized ribbons and that would allow the ribbon and the associated macros to be distributed.

 

[Jake Blackmore]

Hi both - really appreciate your discussions on this as it's really captured my interest these past few days.

My intention is to have in a file that is distributed and I know the users are constantly using different machines (Hot desking internationally to be precise) and whilst I can handle most things natively I wondered if there was something I could do in the ribbon to make it look next level generation. Because the file is distributed and I don't want them installing an add-in each time (even though it's not the hardest thing in the world) I wanted to call it from the VBA. Also I thought it would allow quite a lot of flexibility in what is called if I could use the VBA to call it because then I could 'build it up on the fly' so to speak and if I wanted the user to adjust the ribbon in anyway eg. show a full set of buttons for Admin or limited dependant on user I could call separate XML files.

Surely if we are able to get the ribbon buttons on our ribbon via VBA we must be able to enable callback on them?

 

[RickXL]

Surely if we are able to get the ribbon buttons on our ribbon via VBA we must be able to enable callback on them?

I don't know for sure either way. However, the file you are trying to use is for user enhancements to the Ribbon and QAT. That has no need to support any custom callbacks. So maybe it just has not been included.

You say VBA will make things easier but how do you propose to distribute the VBA?

 

[Jake Blackmore]

Ok perhaps so - They already have a particular template file with some bespoke code. If this file "Excel.OfficeUI" is going to have restrictive functionality then I am forced to consider Add-in's and I guess training / user guides yes?

Jake

I don't know for sure either way. However, the file you are trying to use is for user enhancements to the Ribbon and QAT. That has no need to support any custom callbacks. So maybe it just has not been included.

You say VBA will make things easier but how do you propose to distribute the VBA?

 

[Jaafar Tribak]

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