When working with the Office 2007 CustomUI, you may want to toggle visibility of certain tabs or groups, for example. You accomplish this by using the getVisible attribute of the XML UI code and then wrapping it with a callback to handle the visibility.

In this article, I show you how to toggle the Office 2007 CustomUI visibility for a whole group and a group depending on the active worksheet (I will use Excel as the basis for this example). To begin with, we need to add the XML code to our XLSM file:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" onLoad="rx_onLoad">
 <ribbon >
  <tabs>
   <tab idMso="TabHome" label="My Home Tab" getVisible="rx_getVisibleTab">
    <group idMso="GroupClipboard" getVisible="rx_getVisibleGroupClipboard" />
   </tab>
  </tabs>
 </ribbon>
</customUI>

Notice that we have the following callbacks to generate:

·         rx_onLoad

·         rx_getVisibleTab

·         rx_getVisibleGroupClipboard

The first callback handles the setting of our ribbon object and the default values for the tab and group visibility. The second and third callbacks simply pick up the visibility boolean value and return it as the object’s visibility status (either the entire Home tab or the clipboard group or both).

Next, you should add a module where the following code should be added:

Option Explicit

Public rxMyRibbon                                       As IRibbonUI
Public rxblnTabHomeVisible                              As Boolean
Public rxblnGroupClipboardVisible                       As Boolean

Sub rx_onLoad(ribbon As IRibbonUI)
    Set rxMyRibbon = ribbon
    rxblnTabHomeVisible = True
    rxblnGroupClipboardVisible = True
End Sub

'Callback for TabHome getVisible
Sub rx_getVisibleTab(control As IRibbonControl, ByRef returnedVal)
    returnedVal = rxblnTabHomeVisible
End Sub

'Callback for GroupClipboard getVisible
Sub rx_getVisibleGroupClipboard(control As IRibbonControl, ByRef returnedVal)
    returnedVal = rxblnGroupClipboardVisible
End Sub

Finally, the visibility will depend on which sheet is active, thus in the code module for ThisWorkbook, you should add the following code:

Option Explicit
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim shIndex                    As Integer
   
    shIndex = Sh.Index
   
    Select Case shIndex
        Case 2
            rxblnTabHomeVisible = True
            rxblnGroupClipboardVisible = False
        Case 3
            rxblnTabHomeVisible = False
        Case Else
            rxblnTabHomeVisible = True
            rxblnGroupClipboardVisible = True
    End Select
   
    rxMyRibbon.Invalidate
End Sub

In the above scenario, if the active sheet has an index equal 2 (two); then the tab should be visible but not the clipboard group. When the index is 3 (three) then the whole tab should be invisible. Otherwise, all should be visible.

You can download the sample file here: www.msofficegurus.com/uploads/getVisible.xlsm.

For further information on the Ribbon, you can this book:

In Office 2007, Microsoft introduced what is widely known as the "Office Button". This is a button that functions in the same way as the old "File" menu, which gives you tools to work with the file such as Save As, Print, etc.

Have you ever wondered why the Microsoft Office Button flashes? Or how to make it flash again?

The first thing to know is that the Button flashes for discoverability reasons, that is, it flashes with the implicit and silent message: "Hey, I am here! Click me!". Once you have "discovered" the Office Button, it will stop flashing in all of the Office applications.

Here's a cool trick to get the Office Button to flash again by tweaking the Windows Registry. Have fun!

If you start from scratch, then using the getVisible attribute to show/hide a tab will not work, since you'd have to rebuild the functionalities for the elements. However, an option would be to simply start all tabs hidden and then show/hide as you go along, this would avoid the frustrating of having to rebuild each tab using XML to work with them.

Remember that working with startFromScratch does not stop the user from hitting shortcut keys to access certain functionalities (at least for now), so it is pretty much pointless to start from scratch except for the fact you’d have all the tabs and certain commands hidden from the outset. Here’s an option to work with an Excel file. Use the following XML to built your own custom tab:

<customUI
  xmlns="http://schemas.microsoft.com/office/2006/01/customui"
  onLoad="rxRibbon_onLoad">

  <ribbon
   startFromScratch="false">
   <tabs>
    <tab
     idMso="TabHome"
     getVisible="TabHome_getVisible"/>
   <tab
     id="rxTab0"
     label="My Custom Tab">

     <group
      id="rxGrp0"
      label="My Custom Group">

      <toggleButton
       id="rxtglBtn0" getLabel="rxtglBtn0_getLabel"
       imageMso="HappyFace"
       size="large"
       onAction="rxtglBtn0_Click" />
     </group>
    </tab>
   </tabs>
  </ribbon>
</customUI>

Public rx_oRibbon             As IRibbonUI
Public blnGetLabelState       As Boolean
Public blnHomeTabVisibility   As Boolean

Sub rxRibbon_onLoad(ribbon As IRibbonUI)
  Set rx_oRibbon = ribbon
  blnGetLabelState = False
  blnHomeTabVisibility = False
End Sub

Sub rxtglBtn0_getLabel(control As IRibbonControl, ByRef returnedVal)
  Select Case blnGetLabelState
    Case True
      returnedVal = "Hide 'Home Tab'"
    Case False
      returnedVal = "Show 'Home Tab'"
  End Select
End Sub

Sub TabHome_getVisible(control As IRibbonControl, ByRef returnedVal)
  returnedVal = (blnGetLabelState)
End Sub

Sub rxtglBtn0_Click(control As IRibbonControl, pressed As Boolean)
  blnGetLabelState = pressed
  rx_oRibbon.Invalidate
End Sub