Creating Custom Ribbon Galleries in Microsoft Excel 2007

Summary: Gallery controls are a convenient way to visually display many options on the Microsoft Office Ribbon. Explore Excel files that contain the code to create galleries of labels and images. (7 printed pages)

Ron de Bruin, Microsoft Office Excel MVP

Frank Rice, Microsoft Corporation

May 2009

Applies to: Microsoft Office Excel 2007

Contents

  • Introducing Gallery Controls

  • Creating a Gallery of Labels

  • Creating a Gallery of Images

  • Conclusion

  • Additional Resources

To download a Zip file containing the samples discussed in this article, see Galleries in the Ribbon.

Microsoft Office Fluent Ribbon Gallery controls are a convenient way to visually present the user with different related options. Examples of built-in galleries include the text style gallery in Microsoft Office Word 2007 and the Slide Themes gallery in Microsoft Office PowerPoint 2007. One of the features that make built-in galleries so powerful is the ability to provide live previews of the formatting right in the document. This lets you see the effect of the formatting before actually committing the change.

In addition to the galleries built into the 2007 Microsoft Office System, you can also create custom galleries.

NoteNote

Custom galleries in the 2007 Office system do not allow live in-document previews.

In this article, I discuss a couple of different types of galleries in Excel 2007: a gallery of labels, and a gallery of images. These samples use XML to create a custom tab on the Ribbon with a Gallery control, and Microsoft Visual Basic for Applications (VBA) to give the gallery functionality. Clicking an image or label on the gallery runs a macro unique to that item.

You can also find more information on Ribbon extensibility in the set of three articles titled Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3).

Before getting into the samples, there are a couple of general characteristics of Ribbon attributes that help in the explanation. Whenever you see an attribute end in Mso, this designates a feature built into the Microsoft Office product. For example, insertBeforeMso="TabHome" says to insert the custom tab you are creating before the built-in Home tab. The attribute imageMso="HappyFace" indicates that you are using a built-in image for a control’s icon.

Next, whenever you see an attribute prefaced by get such as getItemLabel, this attribute points to a macro that performs whatever action is needed by the attribute. In the case of getItemLabel, the macro returns a label for the particular item. There are exceptions in that the attribute does not always need to start with get to point to a macro. For example, the onAction attribute of the button control points to the macro that is executed when the button is clicked.

The first sample is an Excel 2007 workbook (RDB Gallery Labels 1.xlsm) with a custom Ribbon tab that contains a gallery control. The gallery displays a grouping of labels as well a button that connects to a hyperlink.

Figure 1. A gallery of labels in Excel

A gallery of labels in Excel

In this sample, you can change almost all of the properties associated with the gallery from within the Visual Basic Editor to include the label text, screen tips, and the actions taken when you click a label or the button.

The following XML is used to create the custom tab and the gallery control.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
   <ribbon startFromScratch="false">
      <tabs>
         <tab id="rxtab" insertBeforeMso="TabHome" label="My Custom Tab">
            <group id="rxgrp" label="My Gallery">
               <!-- Starts the definition of our gallery.-->
               <gallery id="rxgal" 
                   label="My Label Gallery" columns="3" rows="10"
                   imageMso="HappyFace"
                   getItemCount="rxgal_getItemCount"
                   getItemLabel="rxgal_getItemLabel"
                   getItemScreentip="rxgal_getItemScreentip"
                   onAction="rxgal_Click"
                   showItemLabel="true"
                   size="large">
            <!-- Insert a button at the end of the gallery.-->
            <button id="rxbtn"
                    imageMso="RefreshStatus"
                    label="Visit Ron's Excel site..."
                    onAction="rxbtn_Click"/>
               </gallery>
            </group>
         </tab>
      </tabs>
   </ribbon>
</customUI>

Looking at the sample, notice the hierarchy of elements starting with the root element <customUI>, the <ribbon> element, the <tabs> collection with only a single <tab> in this sample, the <group> element to hold a collection of related controls, and then the controls themselves which, in this sample, include the <gallery> and <button> controls.

Notice that each of the elements contains one or more attributes. Use attributes to: specify information that applies to an element such as a label or tooltip; to point to macros that give controls functionality such as retrieving label text or performing some action such as when you click a button; to set a Boolean value such as whether the Ribbon is displayed in Start from Scratch mode where the developer is given a clean slate to create a custom user interface when most of the built-in components are hidden.

I will not discuss every attribute in the gallery but will focus on a few of the more interesting ones. The first is the getItemCount which points to the rxgal_getItemCount callback macro. When you click the gallery control, this macro is called and the number of items that you want in the gallery is returned to Excel, in this case there will be twelve items.

Sub rxgal_getItemCount(control As IRibbonControl, ByRef returnedVal)
'This callback tells the RibbonX how many labels you use in the gallery.
    returnedVal = 12
End Sub

Next, the rxgal_getItemLabel callback from the getItemLabel attribute is called twelve times (the number returned in rxgal_getItemCount). Each time the macro is called, Excel 2007 passes in an index number, one for each call, which is then used to specify the next value in the array to return. By changing the count and adding or subtracting items in the array, you can determine how many labels will be displayed.

Sub rxgal_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)
'This callback runs for every item (label).
'Use this if you want to use the cell values of "A1:A12" on Sheet2 as Label names.
'     returnedVal = Sheets("Sheet2").Cells(index + 1, 1).Value

'This example uses the values in the array for Label names.
    Dim Labelname As Variant
    Labelname = _
    Array("Sheila Webster", _
          "Brian Main", _
          "Susan Zhang", _
          "Anne Walzer", _
          "Andrea Vogel", _
          "Ronda Viescas", _
          "Norman Harker", _
          "Michelle Wells", _
          "Wilma Yang", _
          "Angel Wang", _
          "Raymond Denny", _
          "June Winograd")

    On Error Resume Next
    returnedVal = Labelname(index)
    On Error GoTo 0
End Sub

Next, the rxgal_ getItemScreentip callback from the getItemScreentip attribute is called twelve times (the number returned in rxgal_getItemCount). Each time the macro is called, Excel passes in an index number, one for each call, which is then used to specify the next screen tip in the Tipname array to return. By adding or subtracting items in the array, you can determine how many labels will have a screen tip when you hover the mouse over any one of the labels in the gallery.

Sub rxgal_getItemScreentip(control As IRibbonControl, index As Integer, ByRef returnedVal)
'This callback runs for every item(label).

'Use this if you want to use the cell values of "B1:B12" on Sheet2 for screen tips.
'     returnedVal = Sheets("Sheet2").Cells(index + 1, 2).Value

'This example will use the values in the array for screen tips.
    Dim Tipname As Variant
    Tipname = _
    Array("Tip 1", _
          "Tip 2", _
          "Tip 3", _
          "Tip 4", _
          "Tip 5", _
          "Tip 6", _
          "Tip 7", _
          "Tip 8", _
          "Tip 9", _
          "Tip 10", _
          "Tip 11", _
          "Tip 12")

    On Error Resume Next
    returnedVal = Tipname(index)
    On Error GoTo 0
End Sub

When you click one of the labels in the gallery control, the rxgal_Click macro pointed to by the onAction attribute is run.

Sub rxgal_Click(control As IRibbonControl, id As String, index As Integer)
'Call the macro that belongs to the label when you click one of the labels.
'Example: When you click the first label it runs the macro named "macro_1".
    On Error Resume Next
    Application.Run "macro_" & Format(index + 1, "00")
    On Error GoTo 0
End Sub

The rxgal_Click macro takes as an argument the index number of the label that you clicked. This value is then appended to the literal macro_ and used as a parameter for the Application.Run function to call a macro of the same name. The macro then displays a message box.

Sub macro_01()
    MsgBox "Macro 1"
End Sub
Sub macro_02()
    MsgBox "Macro 2"
End Sub
Sub macro_03()
    MsgBox "Macro 3"
End Sub
……
Sub macro_12()
    MsgBox "Macro 12"
End Sub

The next attribute to look at is onAction for the button. When you click the button, the rxbtn_Click macro is called which executes the FollowHyperlink command and opens the specified Web site. You can use any valid URL here.

Sub rxbtn_Click(control As IRibbonControl)
'This code will run when you click on the button at the bottom of the Gallery
    ActiveWorkbook.FollowHyperlink "http://www.rondebruin.nl/tips.htm"
End Sub

The second sample is also an Excel 2007 workbook (RDB Gallery Pictures 1.xlsm) with a custom Ribbon tab that contains a gallery control. The gallery displays a grouping of pictures as well a button that contains a hyperlink.

Figure 2. A gallery of images in Excel

A gallery of images in Excel

The following XML is used to create the custom tab and the gallery control.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
   <ribbon startFromScratch="false">
      <tabs>
         <tab
            id="rxtab"
            insertBeforeMso="TabHome"
            label="My Custom Tab">
            <group
               id="rxgrp"
               label="My Gallery">
                  <!-- Starts the definition of our gallery.-->
                  <gallery
                     id="rxgal"
                     label="Office Gallery"
                     columns="3" rows="10"
                     itemWidth="200"
                     itemHeight="150"
                     getImage="rxgal_getImage"
                    getItemCount="rxgal_getItemCount"
                     getItemImage="rxgal_getItemImage"
                     getItemScreentip="rxgal_getItemScreentip"
                     onAction="rxgal_Click"
                     showItemLabel="false"
                     size="large">
                  <!-- Insert a button at the end of the gallery.-->
                  <button id="rxbtn"
                     imageMso="RefreshStatus"
                     label="Visit Office Online..."
                     onAction="rxbtn_Click"/>
             </gallery>
         </group>
      </tab>
     </tabs>
    </ribbon>
</customUI>

Many of the attributes in this sample perform just as they do in the previous sample so I will not discuss those here. However, I will discuss new attributes or attributes that differ. For example, look at the getImage attribute which points to the callback rxgal_getImage and retrieves an image for each item in the gallery plus an image for the button.

Dim MyFiles() As String
Dim Fnum As Long

Sub rxgal_getImage(control As IRibbonControl, ByRef returnedVal)
    Dim FilesInPath As String
    FilesInPath = Dir(ThisWorkbook.Path & "\Img\*.jpg")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    Fnum = 0
    Do While FilesInPath <> ""
        Fnum = Fnum + 1
        ReDim Preserve MyFiles(1 To Fnum)
        MyFiles(Fnum) = FilesInPath
        FilesInPath = Dir()
    Loop

    Set returnedVal = LoadPictureGDI(ThisWorkbook.Path & "\Img\" & MyFiles(Fnum))

End Sub

This callback macro does a couple of things: First, it uses the Dir method to test if there are images in the Img folder. This folder must be located in the same directory as the sample Excel workbook file. It then loops through all of the images in the folder and populates an array MyFiles with the image names. It also increments the Fnum variable with the number of images. This counter will be used again shortly. And finally, the procedure returns the last image in the array which is the button image by using the LoadPictureGDI function.You will need to name the pictures in the Img folder 01 nameofpicture, 02 nameofpicture, and so forth. The number will be the position of the picture in the gallery. However, do not use a number in the name of the gallery button image.

NoteNote

The LoadPictureGDI function is used here as a substitute to the VBA LoadPicture method. You can find more information on the LoadPictureGDI function at Stephen Bullen’s Excel page.

Using other image formats is possible just by changing the file extension from .jpg to .png in the code statement FilesInPath = Dir(ThisWorkbook.Path & "\Img\*.jpg").

Next, the rxgal_getItemCount callback from the getItemCount attribute specifies how many times other callback macros will be called (the number of images that you want in the gallery).

Sub rxgal_getItemCount(control As IRibbonControl, ByRef returnedVal)
    returnedVal = Fnum - 1
End Sub

In the callback, I use the variable Fnum (the count of all images in the MyFiles array) from the rxgal_getImage callback macro. The – 1 (minus one) takes into account the picture for the gallery button so the resulting count is nine.

Next, the rxgal_getItemImage callback from the getItemImage attribute is called nine times (the number returned in rxgal_getItemCount).

Sub rxgal_getItemImage(control As IRibbonControl, index As Integer, ByRef returnedVal)
    Set returnedVal = LoadPictureGDI(ThisWorkbook.Path & "\Img\" & MyFiles(index + 1))
End Sub

Each time the macro is called, Excel passes in an index number, one for each call, which is then used to specify the next picture in the MyFiles array to return. By adding or deleting pictures from the Img folder, you can determine how many pictures will be displayed in the gallery.

Conclusion

In the samples associated with this article, you can change almost all of the properties associated with the gallery from within the Visual Basic Editor to include the label text, screen tips, and the actions taken when you click a label, image, or button. However, it is also possible to do most of the things in the workbook. For example, the images can be a part of the workbook so you do not need a separate folder. The downloadable Zip file contains four sample files that you are encouraged to experiment with.

Additional Resources

You can find more information about the topics discussed in this column at the following locations: