Ribbon: add customUI14.xml and "turn on" ribbon in workbook


Board Regular
Nov 29, 2018
Office Version
  1. 2019
  1. Windows
Ribbonmasters: Much feeling around in the dark got me to the point where I create a well-behaved customUI14.xml file and put it into the customUI folder in the workbook file manually. Of course nothing happens when I open the workbook. I'm missing something obvious that I overlooked or forgot during my extensive reading. I know that my xml is good because if I open the workbook containing my xml with RibbonX and then immediately save the workbook that the ribbon shows up when I re-open the workbook. So RibbonX is doing something other than adding/modifying the xml file to/in into the workbook?

In that regard, the best I saw re putting the xml into a workbook is VB, not VBA. I tried hacking it into VBA but I bet that I did a laughable job at best...I am mostly ignorant about Excel and most topics after all.

Here is the hack attempt.

VBA Code:
Public Sub XLAddCustomUI(ByVal fileName As String, _
                         ByVal customUIContent As String)
    ' Add a custom UI part to the document.
    ' Use this sample XML to test:

    '<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    '    <ribbon>
    '        <tabs>
    '            <tab idMso="TabAddIns">
    '                <group id="Group1" label="Group1">
    '                    <button id="Button1" label="Button1"
    '                     showImage="false" onAction="SampleMacro"/>
    '                </group>
    '            </tab>
    '        </tabs>
    '    </ribbon>

    ' In the sample XLSM file, create a module and create a procedure
    ' named SampleMacro, using this signature:
    ' Public Sub SampleMacro(control As IRibbonControl)
    ' Add some code, and then save and close the XLSM file. Run this
    ' example to add a button to the Add-Ins tab that calls the macro,
    ' given the XML content above in the AddCustomUI.xml file.

    'Using document As SpreadsheetDocument = _
        SpreadsheetDocument.Open(fileName, True)
        ' You can have only a single ribbon extensibility part.
        ' If the part doesn't exist, add it.
        'Dim part = document.RibbonExtensibilityPart
        'If part Is Nothing Then
        '    part = document.AddRibbonExtensibilityPart
        'End If
        'part.CustomUI = New CustomUI(customUIContent)
    'End Using
    Dim vDocument As SpreadsheetDocument
    Dim vPart As Variant
    Set vDocument = SpreadsheetDocument.Open(fileName, True)

    With vDocument
        Set vPart = .RibbonExtensibilityPart
        If vPart Is Nothing Then
            vPart = document.AddRibbonExtensibilityPart
        End If
        With vPart
            .CustomUI = New CustomUI(customUIContent) '<= ERROR
            .CustomUI.Save()                          '<= ERROR
        End With

    End With
    With vDocument
    End With
End Sub

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes


Well-known Member
Jan 13, 2020
Office Version
  1. 365
  2. 2010
  3. 2007
  1. Windows
  2. Mobile
  3. Web
What exactly are you needing help with ?

Watch MrExcel Video

Forum statistics

Latest member