How to preserve user's CustomUI Ribbon in Excel using VBA or XML

Question

I have an Excel Add-In with a number of small subs, that upon initializing creates a Custom tab on the Ribbon to control said subs. I use VBA string with XML code to modify Ribbon (see the code below).

The Add-In is meant to be shared with a number of users, most of whom already have some customization in place. The problem is, that Add-In deletes any custom settings user might have on his Excel Ribbon, be it tabs, buttons or a shortcut.

I've played with this problem for a few days and was unable to find a solution. I need it to add a custom tab to an already existing ribbon, rather then overwrite it.

Part of the sub that creates a Custom Tab for an Add-In controls is below

Sub CreateRibbon()

'create a custom ribbon menu'

Dim hFile As Long
Dim path As String, fileName As String, ribbonXML As String

    hFile = FreeFile
    
    path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"
    fileName = "Excel.officeUI"
    
    ribbonXML = "<mso:customUI      xmlns:mso='http://schemas.microsoft.com/office/2009/07/customui'>" & vbNewLine
    ribbonXML = ribbonXML + "  <mso:ribbon startFromScratch='false'>" & vbNewLine
    ribbonXML = ribbonXML + "    <mso:qat/>" & vbNewLine
    ribbonXML = ribbonXML + "    <mso:tabs>" & vbNewLine
                'tab'
    ribbonXML = ribbonXML + "      <mso:tab id='MicroHelpers' label='Helpers' insertBeforeQ='mso:TabFormat'>" & vbNewLine
                'group'
                
        ribbonXML = ribbonXML + "        <mso:group id='MassFormulas' label='Mass Formula' autoScale='true'>" & vbNewLine
            
            ribbonXML = ribbonXML + AddButtonXML("TextToNum", "WordCount", "Text To Num")
            
        ribbonXML = ribbonXML + "        </mso:group>" & vbNewLine

                .....

        ribbonXML = ribbonXML + "        </mso:group>" & vbNewLine
    ribbonXML = ribbonXML + "      </mso:tab>" & vbNewLine
    ribbonXML = ribbonXML + "    </mso:tabs>" & vbNewLine
    ribbonXML = ribbonXML + "  </mso:ribbon>" & vbNewLine
    ribbonXML = ribbonXML + "</mso:customUI>"
    
    ribbonXML = Replace(ribbonXML, """", "")
    
    Open path & fileName For Output Access Write As hFile
    Print #hFile, ribbonXML
    Close hFile

end sub

I'll be grateful for some guidance

Lots of discussion here: stackoverflow.com/questions/8850836/… The thing seems to be that if you add custom UI at run-time you need to remove it later, but not just by clearing the users Custom UI completely, but by returning it to its original state. That's not a simple thing to do (what if another add-in modified it after yours loaded?) so using the customIUEditor tool is the "best" approach. — Tim Williams
@TimWilliams, using an Editor is, indeed, the simplest way, but unfortunately, I can't use any additional software due to my company's policy. Plus, I didn't quite get the delete part of your message. I don't need to clear Ribbon at WorkBook close, the tab should be present at all times. It's like a one-time activation thing, meant to be installed once and used daily. — OKrav
I assumed you were deleting the custom xml when you closed your add-in. FYI you do not need the CustomUI editor - it can all be done other ways eg: stackoverflow.com/questions/20772723/… — Tim Williams

OKrav OKrav · Accepted Answer · 2020-07-23T13:11:19

For all of you, who suffered with this, the same way I did. I've figured it out.

The problem was, that when the end user tried to install an add-in, it overwrote his ribbon customization, deleting everything and showing only my code. The solution turned out to be very simple:

Before creating a new tab, I've extracted the XML code FROM Excel.officeUI file, embedding my code into already existing one

hFile = FreeFile
user = Environ("Username")
path = "C:\Users\" & user & "\AppData\Local\Microsoft\Office\"

Open path & fileName For Input Access Read As hFile
bubble_local = Input(LOF(hFile), hFile)
Close hFile

'main macro part'

Open path & fileName For Output Access Write As hFile
Print #hFile, ribbonXML
Close hFile

This way user's customization are preserved. I don't know why it took me so long, but to me it wasn't obvious. I hope it will save some of you the struggle

BTW, due to the use of Excel.officeUI, I've found that the only way to refresh the ribbon is to reload Excel

How to preserve user's CustomUI Ribbon in Excel using VBA or XML

1 Answers