In the last post I introduced a template which adds a custom tab on the ribbon. The focus of that post was on the plumbing required to handle state loss so, to reduce background noise, the custom tab just had a couple of simple command buttons. In this post I’m going to add a slightly more complicated control to the template’s custom tab: a dropdown.
A dropdown control can essentially come in two flavours:
It can have a static list of items. The items are all defined upfront in the XML as
It can have a dynamic list of items which are added using callbacks when it is initialised.
It’s very easy to add custom images to static dropdown lists, so that’s what I’m going to concentrate on. I’m a bit of a cricket fan so for this example I’ve added a static dropdown which lists the current ICC Test teams. To make it look a bit prettier I’ve included an ICC logo on the dropdown control and the teams’ logos in the dropdown list. Here’s the result:
You can download an example file from here.
The majority of the work was done in the Custom UI Editor. I opened up the RibbonX template from my previous post and inserted the XML highlighted below for the dropdown (note that the element is called a
dropDown and that it is case-sensitive):
<group id="grpRADExcelSheet1" label="RAD Excel Toolkit Sheet1" tag="grpSheet1" getVisible="wstRibbonX.GetVisible"> <button id="cmdForSheet1" imageMso="AccessRefreshAllLists" label="Special Button For Sheet 1" size="normal" onAction="wstRibbonX.cmdForSheet1_onAction" supertip="Only available for sheet1" /> <dropDown id="drpCricketTeams" image="ICC" label="Cricket Teams: " onAction="wstRibbonX.drpCricketTeams_onAction" supertip="Only available for sheet1"> <item id="Australia" label="Australia" image="Australia" /> <item id="Bangladesh" label="Bangladesh" image="Bangladesh" /> <item id="England" label="England" image="England" /> <item id="India" label="India" image="India" /> <item id="NewZealand" label="New Zealand" image="NewZealand" /> <item id="Pakistan" label="Pakistan" image="Pakistan" /> <item id="SouthAfrica" label="South Africa" image="SouthAfrica" /> <item id="SriLanka" label="Sri Lanka" image="SriLanka" /> <item id="WestIndies" label="West Indies" image="WestIndies" /> </dropDown> </group>
Next I downloaded the team logos from the internet and used MS Paint to convert them into 24×24 24-bit Bitmaps. Then I added (Insert > Icons) the team logo images to the project in the Custom UI Editor. Behind the scenes you’ll find that this adds the images into the \CustomUI\Images\ container folder.
The images in the images folder are linked to each
item control by its
image attribute. Similarly, the
dropDown itself has an
image attribute for the ICC logo:
If you want to define the width of the
dropDown control then you can add a
SizeString attribute which I left out. The
dropDown only has one callback,
onAction, for which VBA code needs to be added in the
'Callback for drpCricketTeams onAction Public Sub drpCricketTeams_onAction( _ ByVal control As IRibbonControl, _ ByVal id As String, _ ByVal index As Integer) MsgBox "You picked " & id End Sub
Okay, I’ve kept it simple by just showing a message box with the
id, but you can let your imagination go wild with what you might want to do here. The really nice thing about this set-up is that the images are stored inside the Excel file, so they’ll automatically be available when it’s saved or sent somewhere. I like that a lot.
There are a couple of important limitations though.
Firstly, it’s a rare event for a team to gain or lose ICC Test status, so having them in a static list like this is quite reasonable. But if we had a list which constantly changed then a static list wouldn’t be suitable. Implementing custom images on dynamic dropdown lists isn’t as smooth as it is for static dropdown lists: images in the \CustomUI\Images\ container folder can’t be read from VBA, so they have to be made available outside of the file.
Secondly, the information one can glean in the
onAction callback is limited. For example, the
label attribute of each
item isn’t available. On this point (and depending on what you need to do) a
comboBox could be a better choice, since the ‘label’ (
text) would be available through an
onChange callback. However, a
comboBox would allow the user to freely type in anything they want, so defensive coding would be required to validate the text.