We all know how difficult it was to customize (or personalize, if you prefer) Office 2007. That changed a lot in Office 2010 and everything has become much, much simpler. A major change in this customization of the Ribbon is related to the scaling of groups. Previously, only the standard groups in the Ribbon could collapse. Now, Microsoft introduced the autoScale attribute, which is used to determine if a custom group should or should not collapse when we reduce the size of the Ribbon or the resolution of the screen is reduced.

Check out the picture below (I am actually using Office 2010 in Portuguese, but you will get the picture, if you forgive me the pun):

Figure 1: A Ribbon custom Group expanded

The group "Minhas Ferramentas (My Tools)" at the moment is expanded as usual. But assuming that the configuration of your screen or monitor is changed, you would get the following result with the autoScale set to "true" (autoScale="true"):

Figure 2: Custom Ribbon Group collapsed

If we keep reducing the Ribbon, the group is continuously reduced in size, and it further collapses the elements of our group:

Figure 3: Buttons collapsed to a splitButton

To implement this customization / personalization of the Ribbon just add the attribute autoScale and set its value as "true". Note that this attribute must be determined for each group separately, it does have a global effect on other groups in the Ribbon.

Microsoft Office 2010 (Beta) has come with a nice new feature that allows any earthling to change the looks of his or her user interface, aka, the Ribbon. When Microsoft Office 2007 came into existance, changing the user interface meant getting one's hands dirty in XML code. In Office 2010 things changed for the better, although you still need XML for a more professionally looking customization.

Here’s how this new feature looks like:

Figure 1: Microsoft Office 2010 Ribbon Customization

This looks great and certainly makes life much easier when it comes to customizing the user interface. However, I think that this adds layers to our task. An alternative would be to add an option to the right-click such that we could easily remove a tab, group or button. Also, there should be a button to reset the Ribbon from the same right-click. Here’s how my Ribbon Customization idea would look like:

Figure 2: Remove/Reset Ribbon button straight from the right-click

For many people out there, who have written AddIns for Excel using VB6, they find themselves with a dilemma: how do I add my custom toolbar to another Ribbon tab?

Well, I honestly do not have an answer to that question; however, we can still customize the Ribbon and move this Custom Toolbar to another location. To be specific, we can move it to the QAT (Quick Access Toolbar).

First off, you need to add your custom toolbar. You can use VBA to add this custom toolbar as follows:

Sub test()
    Dim cmdBar As CommandBar
    Dim btn As CommandBarButton

    On Error Resume Next
    Application.CommandBars("Testing").Delete()
    cmdBar = Application.CommandBars.Add
    cmdBar.Name = "Testing"
    cmdBar.Visible = True

    btn = cmdBar.Controls.Add(Type:=msoControlButton)

    With btn
        .Style = msoButtonIconAndCaption
        .FaceId = 986
        .Caption = "Robert Martim"
    End With
End Sub

The above code will add the following to the Add-Ins tab:

Now, you create your other Excel document where you wish to add the GroupAddInsCustomToolbars to it. Open this Excel document using the custom UI Editor where the following XML must be inserted:

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

      xmlns:mso="http://schemas.microsoft.com/office/2006/01/customui">

      <ribbon startFromScratch="true">

       <qat>

        <documentControls>

         <mso:control idQ="mso:GroupAddInsCustomToolbars" visible="true"/>

        </documentControls>

       </qat>

      </ribbon>

</customUI>

The GroupAddInsCustomToolbars is placed under the mso namespace; therefore we must refer to it and then get the idQ for it. Remember that GroupAddInsCustomToolbars is not a group per se, but a control (it is a commandbar control). Hence, if you try to place it under a tab, it fails since it cannot find the group.

Also, remember that to customize the QAT you must start from scratch. The final result is:

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: