I have .xlsb file with user ribbon (i made it in "Custom UI Editor For Microsoft Office").
I want automatically delete (or hide) this ribbon from file (with VBA). Is it possible?
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
[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