Who Says The Ribbon Is Hard?

Bob Phillips — Tue, 06 Mar 2012 20:41:00 GMT

Introduction

I was recently chatting with a friend, and he was asking how you can have dynamic ribbon buttons, buttons that are available depending upon worksheet events. I knocked up a simple example, and I thought I would share it here for anyone else who might be interested. It takes a few steps, but it is remarkably easy.

The example has three buttons within a single group, on a custom tab. The first button can be hidden by changing a cell value (a data validation cell in this case), or have its visibility restored. The second does nothing, whilst the third can change the image when a certain cell is selected, from one image to another. This principle could easily be extended to have say different groups of buttons for each sheet in a workbook, hide/expose the group upon activation/deactivation of the sheet.

CustomUI Editor

If you want to look at the xml, you should download the CustomUI Editor. It is hardly what you would call a sophisticated tool, but it does enable you to create xml for the ribbon customisations.

Coding Techniques

A couple of things about my code. I do not hardcode any values in the xml, apart from the ids of the code. Instead I use callbacks for all of the properties. As an example, I could setup a button in the xml like so

which has all of the properties, except onAction, hard-coded.

I could do it this way, but I don’t, instead my xml looks like this

where all of the properties are using callbacks, which means that the value gets set within the VBA code. This is the code for the rxGetLabel callback

Ribbon Code

Public Function rxGetLabel(control As IRibbonControl, ByRef label)

   Select Case control.Id

       Case CONTROLID_TAB_DYNA:                 label = LABEL_TAB_DYNA
       Case CONTROLID_GRP_DYNA:                 label = LABEL_GRP_DYNA
       Case CONTROLID_BTN_DYNA_1:             label = LABEL_BTN_DYNA_1
       Case CONTROLID_BTN_DYNA_2:             label = LABEL_BTN_DYNA_2
       Case CONTROLID_BTN_DYNA_3:             label = LABEL_BTN_DYNA_3
   End Select
End Function

It simply tests for the control’s id, and returns the caption for the label appropriately. All of the values tested for and the values returned are stored as constants in the VBA (they could just as easily be stored in a table on a worksheet). The values of all of those constants in the above code snippet are

' CONTROLID_ constant values
Global Const CONTROLID_TAB_DYNA As String = "tabDynaRibbon"
Global Const CONTROLID_GRP_DYNA As String = "grpDynaRibbon"
Global Const CONTROLID_BTN_DYNA_1 As String = "btnDynaRibbon1"
Global Const CONTROLID_BTN_DYNA_2 As String = "btnDynaRibbon2"
Global Const CONTROLID_BTN_DYNA_3 As String = "btnDynaRibbon3"

' LABEL_ constant values
Global Const LABEL_TAB_DYNA As String = "Dyna-Ribbon"
Global Const LABEL_GRP_DYNA As String = "Dynamic Ribbon"
Global Const LABEL_BTN_DYNA_1 As String = "Button 1"
Global Const LABEL_BTN_DYNA_2 As String = "Button 2"
Global Const LABEL_BTN_DYNA_3 As String = "Button 3"

Managing Changes

Taking one of the dynamic elements, the first button that can be made non-visible, the visible value is not a constant, but a variable as it has to be manipulated by the code

' VISIBLE constant values

Global VISIBLE_BTN_DYNA_1 As Boolean

This variable is initialised as TRUE in the Workbook_Open event, and in the callback procedure, the control’s visibility property is returned as whatever is in this variable

Public Function rxGetVisible(control As IRibbonControl, ByRef Visible)

Select Casecontrol.Id

Case CONTROLID_BTN_DYNA_1: Visible = VISIBLE_BTN_DYNA_1

etc.

The visibility variable gets changed in simple worksheet event code (remember, I am using a data validation cell to drive this code).

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address

Case "$C$3"

VISIBLE_BTN_DYNA_1 = Target.Value = "Visible"

End Select

mgrxIRibbonUI.Invalidate
End Sub

As you can see, it sets the variable depending upon the cell value. Finally, it invalidates the ribbon variable so as to rebuild our ribbon customisations. The ribbon variable gets set in the ribbon onLoad callback

Public Function rxDMRibbonOnLoad(ribbon As IRibbonUI)

Set mgrxIRibbonUI = ribbon
End Function

Invalidating the ribbon variable causes the callback routines to be re-entered; this is how you change things dynamically.

Summary

Hopefully you can see how simple it is to create dynamic ribbon customisations, and how to manage the changes within your VBA code. You should also be able to see how this can be extended to other areas of dynamic interaction.

As I mentioned, I keep the CustomUI very simple, and rely on the VBA code to set all of the properties of my ribbon customisations. This is not necessary, it is a style aspect that I prefer, I like to the XML simple, reducing the amount of changes that I need to make there, and instead drive it all from VBA. I use a host of global constants for my ribbon values, but again as mentioned earlier, it would be just as simple (and maybe pro vide more manageability of the values) if all of the control properties were added as a table on a hidden worksheet, and grabbed from there on ribbon load.

My example workbook is attached .