Figure 2: Excel 2003 Classic AutoFormat dialog box

Simply choose the AutoFormat you want and click OK. You’re set to go.

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: