OfficeTalk: Display and Hide Tabs, Groups, and Controls on the Microsoft Office Ribbon User Interface (Part 1 of 2)
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Summary: Learn about tips and tricks for working with custom and built-in tabs, groups, and controls on the Ribbon and the Microsoft Office button. Includes links to sample Microsoft Excel workbooks containing all of the sample code discussed in this column. (9 Printed Pages)
Ron de Bruin, Excel MVP
Frank Rice, Microsoft Corporation
August 2009
Applies to: Microsoft Excel 2007
Contents
In This Column
Changing the Visibility of Ribbon Tabs
Displaying and Hiding Groups and Controls
In This Column
In the following sections, you will see samples showing how to use the getVisible attribute to display or hide various parts of the Microsoft Office Fluent Ribbon user interface (UI). As with all Ribbon customizations, the XML markup defines the structure of the Ribbon and the Visual Basic for Applications (VBA) code gives each component its functionality. For more detailed information on customizing the Ribbon UI, see the series of three articles titled Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3).
Sample workbooks containing all of the XML markup and VBA code described in this column plus more can be downloaded at Ron de Bruin’s Web site.
A great tool for inserting or examining the XML that defines the custom Ribbon components in a Microsoft Office file is the Custom UI Editor. This tool not only makes inserting XML into a document easy, it also has a validation feature and can create the callback procedure signatures for you.
Changing the Visibility of Ribbon Tabs
In this first section, you work with a custom tab in the Hide-Display-Tab.xlsm workbook. You display or hide the custom tab by clicking the Make Visible button or the Hide button, respectively, in the worksheet. The following markup is the XML that describes the controls and attributes, which make this work. You can also see the XML by opening the Hide-Display-Tab.xlsm file in the Custom UI Editor tool.
<?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="My Tab" insertAfterMso="TabHome" getVisible="GetVisible" tag="MyPersonalTab" >
<group id="customGroup1" label="Group 1">
<button id="customButton1" label="Caption 1" size="normal" onAction="Macro1" imageMso="DirectRepliesTo" />
<button id="customButton2" label="Caption 2" size="normal" onAction="Macro2" imageMso="AccountMenu" />
<button id="customButton3" label="Caption 3" size="normal" onAction="Macro3" imageMso="RegionLayoutMenu" />
</group>
<group id="customGroup2" label="Group 2">
<button id="customButton4" label="Caption 4" size="normal" onAction="Macro4" imageMso="TextAlignGallery" />
<button id="customButton5" label="Caption 5" size="normal" onAction="Macro5" imageMso="PrintPreviewClose" />
<button id="customButton6" label="Caption 6" size="normal" onAction="Macro6" imageMso="PrintPreviewShrinkOnePage" />
<separator id="MySeparator1" />
<button id="customButton7" label="Caption 7" size="large" onAction="Macro7" imageMso="ReviewPreviousComment" />
</group>
<group id="customGroup3" label="Group 3">
<menu id="MyDropdownMenu" label="My Menu" size="large" imageMso="TextAlignGallery" >
<button id="customButton8" label="Caption 8" onAction="Macro8" imageMso="TextAlignGallery" />
<button id="customButton9" label="Caption 9" onAction="Macro9" imageMso="TextAlignGallery" />
<button id="customButton10" label="Caption 10" onAction="Macro10" imageMso="TextAlignGallery" />
<button id="customButton11" label="Caption 11" onAction="Macro11" imageMso="TextAlignGallery" />
<button id="customButton12" label="Caption 12" onAction="Macro12" imageMso="TextAlignGallery" />
</menu>
</group>
</tab>
</tabs>
</ribbon>
</customUI>
This XML creates the custom tab displayed in Figure 1.
Figure 1. The custom My Tab tab and controls
The XML defines a tab named My Tab and specifies that the tab be inserted after the built-in Home tab by the insertAfterMso="TabHome" statement. The tab consists of three groups containing a number of buttons and a menu control. Of particular importance are the getVisible attribute, and the tag attribute which is set to MyPersonalTab. The tag attribute is used in the VBA code to tell Microsoft Office which tab to show or hide. The getVisible attributes points to the following GetVisible callback method.
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
Initially, the My Tab tab is hidden when the workbook is opened. However, you can change this by adding the following method to the Workbook_Open event in the ThisWorkbook module.
Private Sub Workbook_Open()
MyTag = "show"
End Sub
When the workbook is first opened, this code sets the MyTag variable to the value show. Then, when the GetVisible procedure is executed, the first thing it does is test to see if the MyTag variable is equal to show. If it is, then True is returned to Microsoft Office, indicating that the tab should be displayed.
To see how the remainder of the GetVisible method works, you need to look at the following procedure.
Sub DisplayRibbonTab()
Call RefreshRibbon(Tag:="MyPersonalTab")
End Sub
The sample workbook contains a worksheet button titled Make Visible, which when clicked, calls the DisplayRibbonTab procedure. This procedure in turn calls the RefreshRibbon method passing in the Tag variable which is set to the value MyPersonalTab. Remember that this is the value of the tag attribute for the My Tab tab.
Note
You can display or hide any custom tab or tabs just by setting the tag attribute to MyPersonalTab. You can display or hide any control whose tag attribute starts with My by using the Call RefreshRibbon(Tag:="My*") statement. You can show or hide all custom tabs, groups, and controls by using the Call RefreshRibbon(Tag:="*") statement.
Continuing with the discussion, when the RefreshRibbon method is called, the first thing it does is set the MyTag variable equal to the passed in Tag parameter value which, in the case, is MyPersonalTab.
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
Assuming there are no problems with the Ribbon, the procedure then calls the Ribbon’s Invalidate callback method. When the Ribbon is invalidated, the callback procedures for all of the tabs, groups, and controls are called. When this happens, the tab’s GetVisible method is called. Because the MyTag variable is now equal to the value MyPersonalTab, the first condition of the If… Else statement is executed which returns True to Microsoft Office. Notice that the visible argument has been passed to the GetVisible method by reference. This means that when the variable’s value is changed in the GetVisible method, that change is reflected in the original argument in the calling procedure. Because the argument was passed by Microsoft Office, the change tells Microsoft Office to display the tab.
If you click the Hide button in the worksheet, the HideEveryTab method is called.
Sub HideEveryTab()
Call RefreshRibbon(Tag:="")
End Sub
This procedure calls the RefreshRibbon method passing in the Tag variable equal to an empty string (""). In that method is the following If…Else conditions that tests the value of the control’s (the My Tab tab) Tag property. In this case, it is equal to an empty string and so control is passed to the Else condition that sets the visible variable equal to False thus hiding the tab.
If control.Tag Like MyTag Then
visible = True
Else
visible = False
End If
Displaying and Hiding Groups and Controls
In this section, the XML markup creates two custom groups on the built-in Home Tab in the Hide-Display_Group.xlsm sample file. The workbook consists of four buttons, as shown in Figure 2, which enable you to display or hide the custom groups and controls.
Figure 2. Four buttons work to display or hide the custom groups and controls
The XML to create the custom groups and controls is shown as follows:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI onLoad="RibbonOnLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
<ribbon>
<tabs>
<tab idMso="TabHome" >
<group id="MyCustomGroup1" label="Group1" getVisible="GetVisible" tag="MyPersonalGroup" >
<button id="customButton1" label="Caption 1" size="normal" onAction="Macro1" imageMso="DirectRepliesTo" />
<button id="customButton2" label="Caption 2" size="normal" onAction="Macro2" imageMso="AccountMenu" />
<button id="customButton3" label="Caption 3" size="normal" onAction="Macro3" imageMso="RegionLayoutMenu" />
</group>
<group id="MyCustomGroup2" label="Group2" >
<button id="customButton4" label="Caption 4" size="large" onAction="Macro4" imageMso="DirectRepliesTo" />
<button id="customButton5" label="Caption 5" size="large" onAction="Macro5"
imageMso="AccountMenu" getVisible="GetVisible" tag="MyPersonalControl" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
The XML creates the custom groups and controls shown in Figure 3.
Figure 3. The custom groups and controls
The XML adds two custom groups to the built-in Home tab. On the groups, a number of buttons are added. By default, Group1 and the Caption 5 button in Group2 are hidden. You can display the hidden group and button by default by using the same technique of setting MyTag = "show" in the Workbook_Open event in the ThisWorkbook module. This is described in the previous section of this column.
If you are comfortable with the code and techniques described in the first section of this column, you will see that the callback procedures used in this section are similar. For example, when you click the Show Only Group worksheet button, the following procedure is executed:
Sub ShowTabGroupControlWithCertainTag_1()
Call RefreshRibbon(Tag:="MyPersonalGroup")
End Sub
This procedure calls the RefreshRibbon method, passing in the Tag argument, which is set to MyPersonalGroup. This is also the tag attribute value for the first group. When the RefreshRibbon method is executed, assuming no problems with the Ribbon, the Ribbon is invalidated which causes all of the callback procedures to be executed.
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
In particular, the GetVisible method is called from the Group1 group. Because the value of the MyTag variable equals the tag attribute of the group, the visible variable is set to True, which tells Microsoft Office to display the group.
If control.Tag Like MyTag Then
visible = True
Else
visible = False
End If
Likewise, clicking the Show Group and Control button calls the ShowTabGroupControlWithCertainTag_3 method.
ShowTabGroupControlWithCertainTag_3()
Call RefreshRibbon(Tag:="My*")
End Sub
This procedure calls the RefreshRibbon method, passing in the My* value. This value contains a wildcard character (*), which means that any control that has a tag attribute value which begins with My, will be affected. In this case, Group1 has a tag attribute equal to MyPersonalGroup and the Caption 5 button has a tag attribute equal to MyPersonalControl; thus both of these controls will be displayed.
The other buttons on the worksheet work in a similar fashion.
Conclusion
As you have seen in this column, creating custom tabs, groups, and controls is relatively easy. Additionally, the getVisible attribute gives you a lot of control over the components making up the Ribbon. In part two of this column, you will see even more uses of the getVisible attribute.