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:
The sample document with the finished RibbonX and VBA code is here: http://officeblogs.net/excel/Charts_In_Ribbon.zip.
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
Tools and Utilities for the 2007 Microsoft Office System
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:
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:
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â€
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
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
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)
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
â€œ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”
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”)
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
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
supertip = sTooltip
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
label = ActiveSheet.ChartObjects(index + 1).Name
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
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)
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.
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.
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.
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