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

OaklandJim

Board Regular
Joined
Nov 29, 2018
Messages
70
Office Version
  1. 2019
Platform
  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>
    '</customUI>

    ' 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)
        'part.CustomUI.Save()
    
    '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
        .Save
        .Close
    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

CSmith

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

Watch MrExcel Video

Forum statistics

Threads
1,114,108
Messages
5,545,987
Members
410,718
Latest member
ALM1GHTY
Top