Back
Excel

Sam Radakovitz on Dynamic Ribbon Galleries

Today’s author: Sam Radakovitz, a program manager on the Excel team that enjoys creating VBA applications in Excel.

When I went to create my first Excel 2007 Ribbon application, I found it a bit difficult to get the right RibbonX and VBA functions setup correctly.  I wasn’t quite sure on the RibbonX attributes … and I wasn’t sure on the arguments to the VBA functions or when the ribbon would call them.  I kept thinking if I would have had a sample Excel workbook to reference I’d probably pick things up a bit easier.

That was a while ago, since then there has been a lot of documentation with sample code added to the MSDN and Office websites.  Its good documentation and will help folks on building their RibbonX applications, but I wanted to create an article with a bunch of links to the appropriate RibbonX documentation and has a sample Excel workbook to go along with it.

This blog post will demonstrate how to place dynamic images of charts into a ribbon gallery via RibbonX and VBA in Excel.  We will be creating two galleries, one with small images of the charts with labels and one with large images:


Small Gallery


Large Gallery

The sample document with the finished RibbonX and VBA code is here: http://officeblogs.net/excel/Charts_In_Ribbon.zip.
 
THE RIBBONX

We’ll start off with the RibbonX code.  If you aren’t familiar with RibbonX, the short explanation is that it’s the XML that defines what the Ribbon will look like.  Specifically for us, the RibbonX will define where we want to put our two galleries, the gallery buttons, and the items in the gallery.  For more detailed information on RibbonX, see these links:

Office Fluent Ribbon Developer Portal
http://msdn2.microsoft.com/en-us/office/aa905530.aspx

Tools and Utilities for the 2007 Microsoft Office System
http://msdn2.microsoft.com/en-us/office/aa905356.aspx

The RibbonX we will create will live in the document and travel with it.  We will have to use a tool to get the RibbonX in the document.  The one I’ll use for this example is called the “Office 2007 Custom UI Editorâ€.  You can download it freely from here:

http://openxmldeveloper.org/articles/CustomUIeditor.aspx

After you download and install the tool, boot it up and open the “charts_in_ribbon.xlsm†document that accompanies this blog entry.  You should see:

The tool found the existing RibbonX in the document and loaded it.  If you were to create a new document and open it, you wouldn’t see any existing RibbonX stored in the document.

The following sections highlight the important points about this Ribbon XML:

Callbacks
For the galleries, instead of explicitly calling out the items that will be in the gallery, we’ve told the ribbon to call our VBA functions to get things like: the count of items in the gallery, the label for the item, and the tooltip that will be displayed when hovering over an item.

Sample from the RibbonX:  getItemCount=â€GetItemCountâ€

 

The red getItemCount is the RibbonX attribute that tells the ribbon to call a VBA function to get the count of items, and the blue GetItemCount is the name of the VBA function to call.  If you wanted to specify a static item count, you could use: ItemCount=â€4â€

Ribbon Object

Adding onLoad=â€ribbondLoaded†to the customUI tag will allow our VBA code to get a handle on the ribbon object which will allow us to ‘invalidate’ the ribbon.  Invalidating the ribbon will force the ribbon to call our gallery routines (the callbacks) the next time the user enters the gallery, giving us a way to update the items and images in the gallery.  I will cover how to invalidate the ribbon in the VBA section.

Positioning of the galleries

For this example, we’re creating a new group on the home tab: idMso=â€TabHomeâ€.  Because we didn’t specify an “insertbefore†property, it will stick our new buttons at the right end of the home tab.  If you’re wondering how to get the names of the existing tabs (TabHome), there is a document that has a list of all the existing ribbon control ids available for download at this link:

2007 Office System Document: Lists of Control IDs
http://go.microsoft.com/?linkid=6365496

Image on the Gallery buttons

I’ve selected an existing ribbon icon for our gallery button images:

This is defined by imageMso=â€ChartPlacementâ€.  You could have a different icon for each, or load your own icon through either a callback or through defining an image property.  If you’re wondering how to get the existing ribbon icon names, there is another document you can download that has a list of them:

2007 Office System Add-In: Icons Gallery
http://www.microsoft.com/downloads/details.aspx?familyid=12b99325-93e8-4ed4-8385-74d0f7661318&displaylang=en

Small Gallery vs. Large Gallery

Both of the galleries have the same callbacks to the code, since their count of items and chart pictures will be the same.  The difference between them is in the RibbonX:

• For the small gallery, the itemWidth and itemHeight properties are set to be much smaller than the large gallery.
• The small gallery has an additional callback defined for the label to each chart in the gallery.  Since the charts are smaller there, adding a label can help folks identify the chart.
• There are fewer columns for the smaller gallery, but more rows.  I’ve set the smaller gallery to a single column to help create a visual difference between the smaller gallery and the larger one for this example.

For a complete list of attributes and properties for RibbonX, see this link:

Customizing the 2007 Office Fluent Ribbon for Developers (Part 2 of 3)
http://msdn2.microsoft.com/en-us/library/aa338199.aspx

VBA CODE

For this section, I’ll walk through each routine and describe what is going on.

 

The oRibbon object is the Ribbon, and it’s what we will use to invalidate the Ribbon so we can force the galleries to be refreshed.

 

 

Public oRibbon As IRibbonUI

 

 

This next sub is called when the ribbon loads our RibbonX and it passes us the Ribbon object,  as far as I know it’s the only way to get the Ribbon object.  The code sets the public ribbon object for later use.

 

 

Public Sub ribbonLoaded(Ribbon As IRibbonUI)

    Set oRibbon = Ribbon

End Sub

 

 

“getItemCount†is called when the user is clicking on the gallery and the ribbon needs to figure out what it should show.  Since we defined this callback in the RibbonX, its calls us as asks how many items there should be in this gallery.

 

A special note on the “OnTime†method, for this example this is how the ribbon is getting “invalidatedâ€.  By default, the ribbon will call all your callbacks once to get the items and images and cache the values till you say they are invalid.  What I’m doing here is making the images stale each time the user clicks on the gallery.  There are smoother ways of doing this like trapping for changes in the charts and ranges they are bound to, but for this example we’ll keep it simple.

 

 

Sub getItemCount(control As IRibbonControl, ByRef count)

    count = ActiveSheet.ChartObjects.count

    Application.OnTime DateAdd(“s”, 1, Now), “InvalidateRibbon”

End Sub

 

 

This next routine is called for each item in the gallery and will set the image displayed in the gallery for that item.  In this example, we want all of our chart images there, so as the Ribbon calls us with an index, we just tell the chart of that index to spit out an image, and then tell the ribbon to use that image.

 

 

Sub getItemImage(control As IRibbonControl, index As Integer, ByRef image)

    ActiveSheet.ChartObjects(index + 1).Chart.Export ThisWorkbook.Path & “Chart_” & index + 1 & “.jpg”, “jpg”

   

    Set image = LoadPicture(ThisWorkbook.Path & “chart_” & index + 1 & “.jpg”)

End Sub

 

 

The “getItemID†routine is called by the ribbon to get an id for the gallery item.  Since we don’t use it in this example, it doesn’t really matter what we set it to.

 

 

Sub getItemID(control As IRibbonControl, index As Integer, ByRef id)

    id = “Chart_” & index

End Sub

 

 

This next callback defines the tooltip, or super tooltip I should say, for each gallery item.  In this example, the tooltip is the series name and formula to give the user an idea of what is there and where it is coming from.  Not super helpful, but interesting.

 

 

Sub getItemSupertip(control As IRibbonControl, index As Integer, ByRef supertip)

    Dim oSeries As Series

    Dim sTooltip As String

   

    For Each oSeries In ActiveSheet.ChartObjects(index + 1).Chart.SeriesCollection

        sTooltip = sTooltip & vbCrLf & oSeries.Name & vbCrLf & oSeries.Formula & vbCrLf

    Next oSeries

   

    supertip = sTooltip

End Sub

 

 

The “getItemLabel†callback is only called by the small gallery, as defined by the RibbonX, and it will set the label for the gallery item.  In this example the chart title is being used as the label, if one isn’t present, it falls back to the chart name.

 

 

Sub getItemLabel(control As IRibbonControl, index As Integer, ByRef label)

    If ActiveSheet.ChartObjects(index + 1).Chart.HasTitle Then

        label = ActiveSheet.ChartObjects(index + 1).Chart.ChartTitle.Caption

    Else

        label = ActiveSheet.ChartObjects(index + 1).Name

    End If

End Sub

 

 

This next routine is called after a user clicks on a gallery item.  For this example, the user is clicking on a chart, so the code scrolls the chart into view and activates it.

 

 

Sub galRefreshAction(control As IRibbonControl, selectedId As String, selectedIndex As Integer)

    ActiveWindow.ScrollIntoView ActiveSheet.ChartObjects(selectedIndex + 1).Left, ActiveSheet.ChartObjects(selectedIndex + 1).Top, ActiveSheet.ChartObjects(selectedIndex + 1).Width, ActiveSheet.ChartObjects(selectedIndex + 1).Height

    ActiveSheet.ChartObjects(selectedIndex + 1).Activate

End Sub

 

If you’re wondering how to define the routines, like how many arguments your routine should have for each callback and what they are, there is a document that has a list of them here:

Customizing the 2007 Office Fluent Ribbon for Developers (Part 3 of 3)
http://msdn2.microsoft.com/en-us/library/aa722523.aspx

The Finished Product

After defining the RibbonX and VBA code, opening up the document will display our two new galleries, and dropping down each will display the most recent images of the charts on your sheet, and clicking a chart in the gallery will take you to it.

Improvements to be made
This example is quick and small to show folks how to get started and to try and not over complicate things.  So that leaves plenty of room for improvements:

Make this an Add-in
Currently this example is just a workbook.  With a little work, making this an add-in would allow you to use it with all your workbooks.

More Charts
Right now the example loops through the active sheet’s charts.  You could easily see how this would be nicer by displaying the charts in the entire workbook, or in all the opened workbooks even.

Image Scaling
The large size I chose fits well for this example, but charts come in all sizes, and can cause the gallery to appear pretty goofy.  Some VBA code to scale the image size and add white space where needed would help out here.

Smarter Caching of Chart images
As I mention above, this example throws out all the images and recreates them every time the gallery is dropped down.  Adding some code to detect changes and only toss out some chart images when needed would be a nice performance and efficiency gain.

Error Checking :-)
I left all the error checking out just to get to the meat of the code.

There are probably other improvements to be had that aren’t coming to my mind as well, post them here, along with any other changes or ideas you think are cool :-)