OfficeTalk: Display and Hide Tabs, Groups, and Controls on the Microsoft Office Ribbon User Interface (Part 2 of 2)

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. (7 printed pages)

Ron de Bruin, Excel MVP

Frank Rice, Microsoft Corporation

October 2009

Applies to:   Microsoft Excel 2007

Contents

  • In This Column

  • Setting the Visibility of Contextual Tabs

  • Using the getVisible Attribute with Built-in Tabs

  • Displaying a Different Tab for Each Worksheet

  • Conclusion

  • Additional Resources

  • Acknowledgement

In This Column

In the following sections, you will see more 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.

You can find workbooks with all of the code discussed in this column at Ron de Bruin’s Web site.

You can find the first installment of this two-part series of columns at: OfficeTalk: Display and Hide Tabs, Groups, and Controls on the Microsoft Office Ribbon User Interface (Part 1 of 2)

Setting the Visibility of Contextual Tabs

The Hide-Display-Contextual Tabs.xlsm workbook shows you how you can hide or display the contextual tabs you see when you select certain items such as images (Picture tools) or if you select a cell inside of a table (Table tools). There are nine built-in contextual tabs:

  • TabSetSmartArtTools

  • TabSetChartTools

  • TabSetDrawingTools

  • TabSetPictureTools

  • TabSetPivotTableTools

  • TabSetHeaderAndFooterTools

  • TabSetTableToolsExcel

  • TabSetPivotChartTools

  • TabSetInkTools

The following XML is used to create these contextual tabs. You set the visibility by using the getVisible attribute.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI onLoad="RibbonOnLoad" xmlns="https://schemas.microsoft.com/office/2006/01/customui">
   <ribbon>
      <contextualTabs>
         <tabSet idMso="TabSetSmartArtTools" getVisible="GetVisible" />
         <tabSet idMso="TabSetChartTools" getVisible="GetVisible" />
         <tabSet idMso="TabSetDrawingTools" getVisible="GetVisible" />
         <tabSet idMso="TabSetPictureTools" getVisible="GetVisible" />
         <tabSet idMso="TabSetPivotTableTools" getVisible="GetVisible" />
         <tabSet idMso="TabSetHeaderAndFooterTools" getVisible="GetVisible" />
         <tabSet idMso="TabSetTableToolsExcel" getVisible="GetVisible" />
         <tabSet idMso="TabSetPivotChartTools" getVisible="GetVisible" />
         <tabSet idMso="TabSetInkTools" getVisible="GetVisible" />
      </contextualTabs>
   </ribbon>
</customUI>

The <tabSet> element represents the entire contextual tab. The idMso attribute identifies the built-in tab. Each of the tabs calls the same GetVisible callback. That callback looks like the following:

Public VisibleTrueFalse As Boolean

Sub GetVisible(control As IRibbonControl, ByRef returnedVal)
    returnedVal = VisibleTrueFalse
End Sub

The worksheet in this sample contains a Make Visible button and a Hide button. These buttons use the following procedures, respectively:

Sub DisplayContextualTabs()
    VisibleTrueFalse = True
    Rib.Invalidate
End Sub

Sub HideContextualTabs()
    VisibleTrueFalse = False
    Rib.Invalidate
End Sub

By default, the contextual tabs are hidden. However, you can change this by setting the VisibleTrueFalse variable to True in the Workbook_Open event in the ThisWorkbook module.

 

Using the getVisible Attribute with Built-in Tabs

In this section, you see a handy way to display or hide built-in tabs, both on the Ribbon and from the Microsoft Office button. When you hide built-in tabs by using the startFromScratch attribute, you cannot make individual tabs visible by using the visible or getVisible settings. In addition, you cannot affect the visibility of built-in tabs, groups, or controls by using the InvalidateControl property of the IRibbonControl object. However, a workaround is to replicate the built-in tabs, groups, and controls as custom component (tabs, groups, controls). You can then control their visibility as you can with any custom component. Additionally, you will see how to handle the visibility of components accessible from the Microsoft Office button.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI onLoad="RibbonOnLoad" xmlns="https://schemas.microsoft.com/office/2006/01/customui">
   <commands>
      <command idMso="ApplicationOptionsDialog" enabled="false"/>
      <command idMso="FileExit" enabled="false"/>
   </commands>
   <ribbon startFromScratch="true">
      <officeMenu>
         <button idMso="FileNew" visible="false"/>
         <button idMso="FileOpen" visible="false"/>
         <button idMso="FileSave" visible="false" />
      </officeMenu>
      <tabs>
         <tab id="MyCustomHomeTab" label="Home" insertAfterMso="TabHome" getVisible="GetVisible" tag="ribhome">
            <group idMso="GroupClipboard" />
            <group idMso="GroupFont" />
            <group idMso="GroupAlignmentExcel" />
            <group idMso="GroupNumber" />
            <group idMso="GroupStyles" />
            <group idMso="GroupCells" />
            <group idMso="GroupEditingExcel" />
        </tab>
        <tab id="MyCustomInsertTab" label="Insert" insertAfterMso="TabInsert" getVisible="GetVisible" tag="ribinsert" >
            <group idMso="GroupInsertTablesExcel" />
            <group idMso="GroupInsertIllustrations" />
            <group idMso="GroupInsertChartsExcel" />
            <group idMso="GroupInsertLinks" />
            <group idMso="GroupInsertText" />
        </tab>
        <tab id="MyCustomPageLayoutTab" label="Page Layout" insertAfterMso="TabPageLayoutExcel" getVisible="GetVisible" tag="ribpagelayout">
           <group idMso="GroupThemesExcel" />
           <group idMso="GroupPageSetup" />
           <group idMso="GroupPageLayoutScaleToFit" />
           <group idMso="GroupPageLayoutSheetOptions" />
           <group idMso="GroupArrange" />
        </tab>
        <tab id="MyCustomFormulasTab" label="Formulas" insertAfterMso="TabFormulas" getVisible="GetVisible" tag="ribformulas">
           <group idMso="GroupFunctionLibrary" />
           <group idMso="GroupNamedCells" />
           <group idMso="GroupFormulaAuditing" />
           <group idMso="GroupCalculation" />
        </tab>
        <tab id="MyCustomDataTab" label="Data" insertAfterMso="TabData" getVisible="GetVisible" tag="ribdata">
           <group idMso="GroupGetExternalData" />
           <group idMso="GroupConnections" />
           <group idMso="GroupSortFilter" />
           <group idMso="GroupDataTools" />
           <group idMso="GroupOutline" />
        </tab>
        <tab id="MyCustomReviewTab" label="Review" insertAfterMso="TabReview" getVisible="GetVisible" tag="ribreview">
           <group idMso="GroupProofing" />
           <group idMso="GroupComments" />
          <group idMso="GroupChangesExcel" />
        </tab>
        <tab id="MyCustomViewTab" label="View" insertAfterMso="TabView" getVisible="GetVisible" tag="ribview">
           <group idMso="GroupWorkbookViews" />
           <group idMso="GroupViewShowHide" />
           <group idMso="GroupZoom" />
           <group idMso="GroupWindow" />
           <group idMso="GroupMacros" />
        </tab>
        <tab id="MyCustomDeveloperTab" label="Developer" insertAfterMso="TabDeveloper" getVisible="GetVisible" tag="ribdeveloper">
            <group idMso="GroupCode" />
            <group idMso="GroupControls" />
            <group idMso="GroupXml" />
            <group idMso="GroupModify" />
        </tab>
    </tabs>
  </ribbon>
</customUI>

Looking at the XML, setting the startFromScratch attribute to True hides the Ribbon tabs, the Quick Access Toolbar (QAT), and most of the commands on the Microsoft Office button. However, the New, Open, and Save menu options on the Microsoft Office button are not affected by the startFromScratch attribute. In addition, it does not hide the contextual tabs on the Ribbon; for example, the Format tab that you see when you select a picture on a worksheet.

The <command> element does enable you to work with the file-related commands on the Microsoft Office button. In this case, the Exit Excel and the Excel Options commands on the Microsoft Office button are disabled. The <officeMenu> element enables you to work with the file-related menu options (along the left navigation bar) such as hiding the New, Open, and Save menu items.

The remainder of the XML recreates the built-in tabs by creating custom tabs and then adding built-in groups to them. This gives you the ability to control their visibility as you would for any custom component.In the Hide-Display-Built-in Tabs.xlsm workbook, you find the workaround described previously that you can use if you use startFromScratch = true in your Ribbon customization code.

It is worth mentioning that you can control the visibility of built-in tabs by using the getVisibility attribute as long as the conditions discussed previously are not in effect. For example, the following XML hides specific built-in tabs and built-in group.

Use the Hide-Display-Built-in Tab-Group.xlsm workbook to test this example.

Note

In this example, the control.Id property is used and not the control.tag property because it is not possible to add a tag to a built-in tab, group, or control.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI onLoad="RibbonOnLoad" xmlns="https://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
       <tab idMso="TabHome" getVisible="GetVisible"  />
       <tab idMso="TabReview" getVisible="GetVisible" />
 
       <tab idMso="TabInsert" >
           <group idMso="GroupInsertTablesExcel" getVisible="GetVisible" />
       </tab>
    </tabs>
  </ribbon>
</customUI>

Displaying a Different Tab for Each Worksheet

When working with the Ribbon in Microsoft Excel, you might want to have a different custom tab for each worksheet. For example, you might have a worksheet where you keep track of inventory and need to have a Ribbon tab for that, another sheet where you track production hours, and yet another sheet where you track orders and shipping costs.

In the sample workbook, Different-Tab-For-Each-WorkSheet.xlsm, three custom tabs with buttons and a menu have been created. This is done by the following XML:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<customUI onLoad="RibbonOnLoad" xmlns="https://schemas.microsoft.com/office/2006/01/customui">
  <ribbon startFromScratch="true">
    <tabs>
      <tab id="MyCustomTab1" label="Ron" insertAfterMso="TabHome" getVisible="GetVisible" tag="Xron">
        <group id="customGroup1" label="Ron's Group">
          <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>
    </tab>
    <tab id="MyCustomTab2" label="Dave" insertAfterMso="TabHome" getVisible="GetVisible" tag="Xdave">
        <group id="customGroup2" label="Dave's Group">
          <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>
    </tab>
    <tab id="MyCustomTab3" label="Special" insertAfterMso="TabHome" getVisible="GetVisible" tag="special">
        <group id="customGroup3" label="Special Group">
          <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>

The XML sets the startFromScratch attribute to True to hide the Ribbon and QAT. Then, the code described next is used to display just the custom tabs or combinations of the custom tabs when the workbook is opened. Notice that the tag attribute for each tab is set to a unique value. This value is used to specify which tabs will be displayed or hidden as seen in the Workbook_SheetActivate event procedure.

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

Notice the wildcard characters present in the code which are used where: "X*" is used to display tabs whose tag value starts with "X" (just the Xron and Xdave tabs), "*" is used to display all three of the custom tabs, and "" (the default) will display none of the custom tabs.

The GetVisible procedure sets the visible state value and RefreshRibbon procedure invalidates the Ribbon based on the tag value.

Conclusion

Creating custom tabs, groups, and controls is relatively easy. By using the getVisible attribute, you have a lot of control over the visibility of Ribbon components as well as options available from the Microsoft Office button. We encourage you to experiment with the XML and code in the workbooks that are available as a download. The Custom UI Editor makes this even easier.

Additional Resources

Additional information is available at the following locations.

Acknowledgement

We would like to thank our editor, Linda Lu Cannon, for her help with this work.