Excel Tips & Tricks #494 - How to add a custom ribbon to your workbook part 3

In Tip #492 I demonstrated how to add a custom ribbon to your workbook. In this article, I am going to give an example of a ribbon with a combo box which can be used to update values in your workbook.

This is the ribbon element I find most useful because it allows me to change input assumptions whilst looking at any of the output sheets of my models without cluttering the output sheet itself.

Overview of the example ribbon

Following the instructions in this article will create a ribbon with a ‘Day of Week’ combo box which will allow the user to select a Day of the Week from a dropdown list (taken from the list of days in cells B2:B8), which will then update the value in cell B11.

If the user updates the days of the week values in cells B2:B8 (for example changing them to ‘Mon’, ‘Tue’, etc.) then pressing the ‘Refresh’ button will update the dropdown list to show the new values.

Instructions to create the example ribbon:

In order to create this ribbon, follow the steps in Tip #492 but make the following 3 changes:

1. When you create the workbook as part of step 1, add the days of the week into a list in cells B2:B8 (as per the screenshot above) and name this range ‘List_Days_Of_Week’. Also add an initial value for the selected day of the week to be ‘Sunday’ in cell B11 and create a named range for this cell called ‘Selected_Day_Of_Week’).

If you are unfamiliar with named ranges then see Tip #452.

2. Replace the VBA code in step 2 with the following code:

Option Explicit

Public myRibbon As IRibbonUI

Sub myribbonLoaded(ribbon As IRibbonUI)

    Set myRibbon = ribbon

End Sub

Sub prcRibRefresh(control As IribbonControl) 

    myRibbon.Invalidate

End Sub

Sub rbnDayOfWeek_getText(control As IRibbonControl, ByRef returnedVal)

returnedVal = ThisWorkbook.Names("Selected_Day_Of_Week").RefersToRange.Value

End Sub

Sub rbnDayOfWeek_getItemCount(control As IRibbonControl, ByRef returnedVal)

returnedVal = ThisWorkbook.Names("List_Days_Of_Week").RefersToRange.Count

End Sub

Sub rbnDayOfWeek_getItemLabel(control As IRibbonControl, index As Integer, ByRef returnedVal)

returnedVal = ThisWorkbook.Names("List_Days_Of_Week").RefersToRange.Cells(index + 1).Value

End Sub

Sub rbnDayOfWeek_onChange(control As IRibbonControl, text As String)

ThisWorkbook.Names("Selected_Day_Of_Week").RefersToRange.Value = text

End Sub

3. Replace the XML in step 3 with the following:

<customUI

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

onLoad="myribbonLoaded">

<ribbon>

<tabs>

<tab id="customTab" label="Tips and Tricks 3" insertAfterMso="TabHome">

<group id="gpDayOfWeek" label="Day Of Week Selector">            

<comboBox

id="rbnSelectDayOfWeek"

label="Day of Week:"

getText="rbnDayOfWeek_getText"

getItemCount="rbnDayOfWeek_getItemCount"

getItemLabel="rbnDayOfWeek_getItemLabel"

onChange="rbnDayOfWeek_onChange" />

<button

id="btnRefresh"

size = "large"

label="Refresh"

onAction="prcRibRefresh"

imageMso="PivotChartRefresh" />

</group>

</tab>

</tabs>

</ribbon>

</customUI>

Explanation:

Combo Box

The comboBox element has several callbacks that need to be defined in the VBA code, which do the following:

• getText – This defines what the selector displays when the ribbon is first loaded (in this case the value in the named range ‘Selected_Day_Of_Week’).
• getItemCount – This defines the number of elements in the dropdown list (in this case the number of cells in ‘List_Days_Of_Week’).
• getItemLabel – This defines the values displayed in the dropdown list (in this case the values in ‘List_Days_Of_Week’).
• onChange – This defines what happens when a value is selected from the dropdown list (in this case updates the value in ‘Selected_Day_Of_Week’ to the selected value).

For a more detailed explanation of the XML code for the combo box and the refresh button see Tip #493.

Refresh Button

Unfortunately, the ribbon isn’t clever enough to pick up changes in the underlying workbook automatically, so if the values in ‘Selected_Day_Of_Week’ or ‘List_Days_Of_Week’ are changed then the ‘Refresh’ button needs to be pressed to update the ribbon.

The key line of the VBA code is the call to myRibbon.Invalidate in prcRibRefresh, which is run when the user presses the ‘Refresh’ button in the ribbon and will reset the combo box with the new values in the Workbook. The myribbon object is set when the Ribbon is first loaded via myribbonLoaded.

[Unhelpfully sometimes Excel loses the ribbon object - a workaround for that issue can be found in a post by former Microsoft MVP Rory Archibald at this link].

Summary:

You can then use this method to add as many combo boxes as you want to your ribbon by using this sample code and updating the named ranges and the references to them.

If you develop workbooks with their own ribbons is there another element that you find yourself using frequently? Let me know by sending an email to the Excel community email address (excel@icaew.com) – I’m always keen to learn about other useful features!