excelvba

Delete user ribbon from file


I have .xlsb file with user ribbon (i made it in "Custom UI Editor For Microsoft Office"). enter image description here

I want automatically delete (or hide) this ribbon from file (with VBA). Is it possible?


Solution

  • For this demonstration, you need CustomUI Editor and Excel(Obviously)

    Create a new workbook and save it as say AlexExample.xlsm

    [A] Open VBE, and insert a module. Paste this code in the module

    Option Explicit
    
    Dim Ribn As IRibbonUI
    Public RibnTag As String
    
    Sub RibbonOnLoad(ribbon As IRibbonUI)   
        Set Ribn = ribbon
    End Sub
    
    Sub GetVisible(control As IRibbonControl, ByRef visible)
        Select Case RibnTag
        Case "ShowTab": visible = True
        Case "HideTab": visible = False
        End Select
    End Sub
    
    Sub ShowMyRibbonTab()
        RibnTag = "ShowTab"
        If Not Ribn Is Nothing Then
            Ribn.Invalidate
        Else
            MsgBox "Couldn't get the ribbon object. Please close and restart the workbook"
        End If
    End Sub
    
    Sub HideMyRibbonTab()
        RibnTag = "HideTab"
        If Not Ribn Is Nothing Then
            Ribn.Invalidate
        Else
            MsgBox "Couldn't get the ribbon object. Please close and restart the workbook"
        End If
    End Sub
    

    [B] Open the ThisWorkbook code area and paste this code

    Option Explicit
    
    Private Sub Workbook_Open()
        RibnTag = "ShowTab"
    End Sub
    

    [C] Go to the sheet and create 2 buttons (Form Control). SHOW and HIDE as shown below. Right click on the button and assign the macro ShowMyRibbonTab to SHOW and HideMyRibbonTab to HIDE

    enter image description here

    [D] Next Open CustomUI Editor and open the Excel File from there. Insert customUI.xml and paste the below code there.

    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <customUI onLoad="RibbonOnLoad" 
    xmlns="http://schemas.microsoft.com/office/2006/01/customui">
    <ribbon>
        <tabs>
          <tab id="MyCustomTab" label="AlexsTab" getVisible="GetVisible" tag="AlexsTab" >
        </tab>
    </tabs>
    </ribbon>
    </customUI>
    

    Save and close the file and you are done.

    SAMPLE FILE: https://www.dropbox.com/s/izzbwpcmqtyoibd/AlexExample.xlsm?dl=0

    enter image description here