Hide or Display Ribbon Tab/Group/Control with getVisible
Information
If you are not familiar with creating custom Tabs, Groups or controls on the Ribbon with RibbonX follow the steps on this page first before you start with the examples on this page.
http://www.rondebruin.nl/ribbon.htm
On this page I show you how you can use the getVisible callback to Hide or Display a Custom Tab,
Group or Control on the Ribbon that you create with RibbonX.
To be able to Hide/Display a Tab, Group or Control with code I add getVisible="GetVisible" in the RibbonX line that add the Tab, Group or Control to the workbook. You also see tag="YourTagName" in this xml line, we use this Tag in the code to tell which tab/group/control we want to make visible.
Use the Custom UI Editor to see or edit the RibbonX.
Note: I also paste the RibbonX on the worksheets in the example workbooks so you not need the
Custom UI Editor to see it.
Download Example files
Download the example workbooks first
31-July-2009 (6 files))
1 : Hide-Display-Tab.xlsm
2 : Hide-Display-Group-Control.xlsm
3 : Hide-Display-ContextualTabs.xlsm
4 : Hide-Display-Built-in Tab-Group.xlsm
5 : Hide-Display-Built-in Tabs.xlsm
6 : Different-Tab-For-Each-Worksheet.xlsm
For more info about the example files read the information below and in each example file.
There is also information about the example files above in this articles on MSDN.
http://msdn.microsoft.com/en-us/library/ee390805(office.11).aspx
The Hide-Display-Tab example workbook
The RibbonX in this file create a custom tab named "My Tab" after the Home tab.
Use the Custom UI Editor to see or edit the RibbonX if you want (I also Paste it on this sheet).
By default the Custom Tab is not visible, but you can change this if you want by using
MyTag = "show" in the Workbook_Open event in the ThisWorkbook module.
See the commented code in the ThisWorkbook module if you want the Tab visible by default.
We use a callback named getVisible to be able to use a VBA macro to Hide this Tab or make it visible.
Look at the code in the module "RibbonModule" in the VBA editor (Alt F11)
You see that I add getVisible="GetVisible" in the line that add the custom Tab in the RibbonX
You also see tag="MyPersonalTab" in this xml line, we use this Tag in the code to tell which tab
we want to hide or show.
<tab id="MyCustomTab" label="My Tab" insertAfterMso="TabHome" getVisible="GetVisible" tag="MyPersonalTab" >
If you open the VBA editor (Alt F11) and look in the module named "RibbonModule" you see the following code at the top of the module that you must NOT change.
When you open the workbook it not Display the Custom tab on the Ribbon.
To Make the Tab visible or Hide it we run one of the two macros below
The code will check the tag of the Tab and if it is named "MyPersonalTab" it set visible
to true and if not to False.
Tip: You can also use wildcards in the code like "My*" to make all Tabs visibe with a tag that start with My. This way you can also use one code line to display more then one tab/group/control in one time.
Tip: You can use the same tag name for different Id's.
This way you can also use one code line to display more then one tab/group/control in one time.
The Hide-Display-Group-Control.xlsm example workbook
The RibbonX in this file create two Custom groups on the Home tab with a few buttons.
Use the Custom UI Editor to see or edit the RibbonX if you want (I also Paste it on this sheet).
This example is almost the same as the example for the Custom Tab so read the information on
the worksheet and you will see that it is not so diffecult.
The Hide-Display-Contextual Tabs.xlsm workbook
This example show you how you can hide or display the ContextualTabs.
This are tabs that you see when you select for example a picture (Picture Tools)
Or if you select a cell in a Table (TableTools)
There are 9 Built-in ContextualTabs
TabSetSmartArtTools
TabSetChartTools
TabSetDrawingTools
TabSetPictureTools
TabSetPivotTableTools
TabSetHeaderAndFooterTools
TabSetTableToolsExcel
TabSetPivotChartTools
TabSetInkTools
Read the information good on the worksheet and in the code.
Hide-Display-Built-in Tab-Group.xlsm
Note: If you use startFromScratch = true in your RibbonX look at the next example file.
The RibbonX in this file will add the getVisible callback to two built-in tabs and one group.
<tab idMso="TabHome" getVisible="GetVisible" />
<tab idMso="TabReview" getVisible="GetVisible" />
<tab idMso="TabInsert" >
<group idMso="GroupInsertTablesExcel" getVisible="GetVisible" />
</tab>
If you look at the code you see that we not test the tag like we do in the other examples but the id.
Call RefreshRibbon(Id:="TabHome")
The reason for this is that it is not possible to add a tag to a built-in command.
Read the information good on the worksheet and in the code.
The Hide-Display-Built-in Tabs.xlsm workbook
Because getVisible is not working for Built-in Tabs, Groups or Controls when you use
use startFromScratch = true I show you a workeround here.
The RibbonX in this file use startFromScratch = true to hide the Ribbon and QAT .
It will Create a custom tab for every original tab on the Ribbon with the same name.
It will Add every original group to this custom tabs to duplicate the original tabs.
Because they are custom tabs now you can hide/display the tab you want with VBA code.
Because we use startFromScratch = true and recreate the Ribbon you are sure that no tabs
from add-ins are visible, for example the Acrobat tab if you have that installed.
Read the information good on the worksheet and in the code.
The Different-Tab-For-Each-Worksheet.xlsm workbook
The RibbonX in this workbook create 3 Custom tabs and you can control which tab you want to see on each worksheet with a code line in the Workbook_SheetActivate event in the ThisWorkbook module.
You can also use the CodeName of the worksheet.
This will also work correct if the user change the worksheet name.
You can use this then
Read the information good on the worksheet and in the code.
If you are not familiar with creating custom Tabs, Groups or controls on the Ribbon with RibbonX follow the steps on this page first before you start with the examples on this page.
http://www.rondebruin.nl/ribbon.htm
On this page I show you how you can use the getVisible callback to Hide or Display a Custom Tab,
Group or Control on the Ribbon that you create with RibbonX.
To be able to Hide/Display a Tab, Group or Control with code I add getVisible="GetVisible" in the RibbonX line that add the Tab, Group or Control to the workbook. You also see tag="YourTagName" in this xml line, we use this Tag in the code to tell which tab/group/control we want to make visible.
Use the Custom UI Editor to see or edit the RibbonX.
Note: I also paste the RibbonX on the worksheets in the example workbooks so you not need the
Custom UI Editor to see it.
Download Example files
Download the example workbooks first
31-July-2009 (6 files))
1 : Hide-Display-Tab.xlsm
2 : Hide-Display-Group-Control.xlsm
3 : Hide-Display-ContextualTabs.xlsm
4 : Hide-Display-Built-in Tab-Group.xlsm
5 : Hide-Display-Built-in Tabs.xlsm
6 : Different-Tab-For-Each-Worksheet.xlsm
For more info about the example files read the information below and in each example file.
There is also information about the example files above in this articles on MSDN.
http://msdn.microsoft.com/en-us/library/ee390805(office.11).aspx
The Hide-Display-Tab example workbook
The RibbonX in this file create a custom tab named "My Tab" after the Home tab.
Use the Custom UI Editor to see or edit the RibbonX if you want (I also Paste it on this sheet).
By default the Custom Tab is not visible, but you can change this if you want by using
MyTag = "show" in the Workbook_Open event in the ThisWorkbook module.
See the commented code in the ThisWorkbook module if you want the Tab visible by default.
We use a callback named getVisible to be able to use a VBA macro to Hide this Tab or make it visible.
Look at the code in the module "RibbonModule" in the VBA editor (Alt F11)
You see that I add getVisible="GetVisible" in the line that add the custom Tab in the RibbonX
You also see tag="MyPersonalTab" in this xml line, we use this Tag in the code to tell which tab
we want to hide or show.
<tab id="MyCustomTab" label="My Tab" insertAfterMso="TabHome" getVisible="GetVisible" tag="MyPersonalTab" >
If you open the VBA editor (Alt F11) and look in the module named "RibbonModule" you see the following code at the top of the module that you must NOT change.
Option Explicit Dim Rib As IRibbonUI Public MyTag As String 'Callback for customUI.onLoad Sub RibbonOnLoad(ribbon As IRibbonUI) Set Rib = ribbon End Sub 'Callback to Hide Display the Custom Ribbon Tab Sub GetVisible(control As IRibbonControl, ByRef visible) If MyTag = "show" Then visible = True Else If control.Tag Like MyTag Then visible = True Else visible = False End If End If End Sub Sub RefreshRibbon(Tag As String) MyTag = Tag If Rib Is Nothing Then MsgBox "Error, Save/Restart your workbook" Else Rib.Invalidate End If End Sub
When you open the workbook it not Display the Custom tab on the Ribbon.
To Make the Tab visible or Hide it we run one of the two macros below
Sub DisplayRibbonTab() 'Show only the Tab, Group or Control with the Tag "MyPersonalTab" Call RefreshRibbon(Tag:="MyPersonalTab") End Sub Sub HideEveryTab() 'Hide every Tab, Group or Control(we use Tag:="") Call RefreshRibbon(Tag:="") End Sub
The code will check the tag of the Tab and if it is named "MyPersonalTab" it set visible
to true and if not to False.
Tip: You can also use wildcards in the code like "My*" to make all Tabs visibe with a tag that start with My. This way you can also use one code line to display more then one tab/group/control in one time.
Tip: You can use the same tag name for different Id's.
This way you can also use one code line to display more then one tab/group/control in one time.
The Hide-Display-Group-Control.xlsm example workbook
The RibbonX in this file create two Custom groups on the Home tab with a few buttons.
Use the Custom UI Editor to see or edit the RibbonX if you want (I also Paste it on this sheet).
This example is almost the same as the example for the Custom Tab so read the information on
the worksheet and you will see that it is not so diffecult.
The Hide-Display-Contextual Tabs.xlsm workbook
This example show you how you can hide or display the ContextualTabs.
This are tabs that you see when you select for example a picture (Picture Tools)
Or if you select a cell in a Table (TableTools)
There are 9 Built-in ContextualTabs
TabSetSmartArtTools
TabSetChartTools
TabSetDrawingTools
TabSetPictureTools
TabSetPivotTableTools
TabSetHeaderAndFooterTools
TabSetTableToolsExcel
TabSetPivotChartTools
TabSetInkTools
Read the information good on the worksheet and in the code.
Hide-Display-Built-in Tab-Group.xlsm
Note: If you use startFromScratch = true in your RibbonX look at the next example file.
The RibbonX in this file will add the getVisible callback to two built-in tabs and one group.
<tab idMso="TabHome" getVisible="GetVisible" />
<tab idMso="TabReview" getVisible="GetVisible" />
<tab idMso="TabInsert" >
<group idMso="GroupInsertTablesExcel" getVisible="GetVisible" />
</tab>
If you look at the code you see that we not test the tag like we do in the other examples but the id.
Call RefreshRibbon(Id:="TabHome")
The reason for this is that it is not possible to add a tag to a built-in command.
Read the information good on the worksheet and in the code.
The Hide-Display-Built-in Tabs.xlsm workbook
Because getVisible is not working for Built-in Tabs, Groups or Controls when you use
use startFromScratch = true I show you a workeround here.
The RibbonX in this file use startFromScratch = true to hide the Ribbon and QAT .
It will Create a custom tab for every original tab on the Ribbon with the same name.
It will Add every original group to this custom tabs to duplicate the original tabs.
Because they are custom tabs now you can hide/display the tab you want with VBA code.
Because we use startFromScratch = true and recreate the Ribbon you are sure that no tabs
from add-ins are visible, for example the Acrobat tab if you have that installed.
Read the information good on the worksheet and in the code.
The Different-Tab-For-Each-Worksheet.xlsm workbook
The RibbonX in this workbook create 3 Custom tabs and you can control which tab you want to see on each worksheet with a code line in the Workbook_SheetActivate event in the ThisWorkbook module.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.Name
Case "Ron": Call RefreshRibbon(Tag:="Xron")
Case "Dave": Call RefreshRibbon(Tag:="Xdave")
Case "RonDave": Call RefreshRibbon(Tag:="X*")
Case "All": Call RefreshRibbon(Tag:="*")
Case "Special": Call RefreshRibbon(Tag:="special")
Case Else: Call RefreshRibbon(Tag:="")
End Select
End Sub
You can also use the CodeName of the worksheet.
This will also work correct if the user change the worksheet name.
You can use this then
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Select Case Sh.CodeName
Case "Sheet2": Call RefreshRibbon(Tag:="Xron")
Case "Sheet3": Call RefreshRibbon(Tag:="Xdave")
Case "Sheet4": Call RefreshRibbon(Tag:="X*")
Case "Sheet5": Call RefreshRibbon(Tag:="*")
Case "Sheet6": Call RefreshRibbon(Tag:="special")
Case Else: Call RefreshRibbon(Tag:="")
End Select
End Sub
Read the information good on the worksheet and in the code.