As mentioned in Part 5 of this series, this post will display how to change the value of a Ribbon control from a VBA procedure. For this example, we will continue to work with the case presented in Part 5, using a checkbox.
Basically, the application of the RibbonX code works like this:
- We create our XML markup via the CustomUI editor or manually
- We open the Excel file
- The RibbonX code is executed, and the Ribbon item is created
If Macros are enabled, then the following will occur next:
- Any Workbook_Open macros are executed (providing macros are enabled)
- Any OnLoad macros (specified in the XML code) are executed (providing macros are enabled)
When a Ribbon item is first presented (the first time your controls are shown), RibbonX will also launch “callbacks” to check what values your controls will have.
To effectively work with RibbonX, one of the first things we need to understand is that RibbonX does not work the same way that VBA collections do. There is no collection of RibbonX items, so we need to store all of our values in our own code. To do this, my preference is to create workbook properties and store my values there. This approach offers the benefit that we can easily refer to our RibbonX items, as well as makes it obvious which workbook we’re dealing with.
Modifying the XML Markup
Before we create a custom property to hold our defaults, we might as well get the XML Markup adjustments out of the way. We’re going to add two things to our XML for this example; and OnLoad statement, and a GetPressed statement. The use of these will become apparent later, so for now, change the XML of your file to read:
Creating the Workbook Properties
We are going to create two new workbook properties for both reading and writing. The first will hold the default state of our checkbox. The second will hold our RibbonUI object so that we can refer to it later. All the following code will go in the ThisWorkbook module of the project:
[vb]‘Private variables to hold state of Ribbon and Ribbon controls
Private bChkBox1 As Boolean
Private pRibbonUI As IRibbonUI
Public Property Let chkBox1(b As Boolean)
‘Set value of chkBox1 property
bChkBox1 = b
Public Property Get chkBox1() As Boolean
‘Read value of chkBox1 property
chkBox1 = bChkBox1
Public Property Let ribbonUI(iRib As IRibbonUI)
‘Set RibbonUI to property for later use
Set pRibbonUI = iRib
Public Property Get ribbonUI() As IRibbonUI
‘Retrieve RibbonUI from property for use
Set ribbonUI = pRibbonUI
Integrating the Checkbox Value Property
In addition to the properties we defined, we’ll need one more piece in the ThisWorkbook module, and that is the code to actually set the default value when we open the workbook. Copy the following procedure below the rest in the ThisWorkbook module:
[vb]Private Sub Workbook_Open()
‘Set the default properties of the ribbon controls
chkBox1 = True
If you have continued on from Part 5, then you already have the “CallControl” procedure in a standard module. We’ll need to add a line to it to store the toggled value in our custom workbook property, so adjust your code to match the following:
[vb]Private Sub CallControl(control As IRibbonControl, pressed As Boolean)
‘Tell the user the state of the control when they physically click it.
ThisWorkbook.chkBox1 = pressed
Select Case pressed
MsgBox “The checkbox is checked!”
MsgBox “The checkbox is NOT checked!”
Integrating the RibbonUI Property
Now, as I covered in the introduction, when we open the workbook, our Ribbon items will be created, and the Workbook_Open routine will fire, setting the value of the checkbox to True. How do we get the checkbox to actually return this value, though?
This is where the “OnLoad” procedure that we specified in the XML comes in. It forms the stepping stone to being able to do this. If you recall, I stated that there was no Ribbon collection and we’d have to hold our own objects if we wanted to use them. This is the exact purpose of the OnLoad procedure.
Copy the following code into the Standard module which holds the “CallControl” procedure:
[vb]Private Sub OnLoad(ribbon As IRibbonUI)
‘Set the RibbonUI to a workbook property for later use
ThisWorkbook.ribbonUI = ribbon
What this code does is feed the RibbonUI object into our workbook property, allowing us to “hold onto” it for later use.Â We know have a way to access our Ribbon object!
“Great!”, you say, “But how?”
Before we go there, we want to do just one more thing.Â We are going to set up one more routine to feed information back to the RibbonUI.Â Remember that we added a “GetPressed” specification to the XML?Â This also needs to call a procedure, (again in the standard module,) which looks like this:
[vb]Private Sub GetPressed(ByVal control As IRibbonControl, ByRef returnVal)
‘Query the property for the value of the chkbox, and feed it back
‘to the ribbon so that it can be set appropriately
If control.ID = “chkbox1″ Then returnVal = ThisWorkbook.chkBox1
The purpose of this routine is to feed the checkbox value back to the RibbonUI so that it can be updated to reflect the value we have on hand for it.Â It can be initiated in a couple of different ways.Â The first time the control is presented, this routine will be called to return the default value.Â Also, we can invalidate the XML, which will trigger this routine as the Ribbon is “rebuilt”.Â The first happens naturally after the Ribbon is loaded, but it’s the second that we’re interested in, as it gives us the ability to rebuild the Ribbon control when we want to change the value.
Updating the Ribbon Control’s Value via VBA
And here it is, at long last.Â A very simple routine to do exactly that.Â We’re going to pretend that we have a lot more code, and a real reason to do this.Â ;)Â At some point, we decide that we need to toggle the value of the checkbox and make it reflect in the menu.Â This routine works by toggling the checkbox property and invalidating the XML for the menu item, which forces the “GetPressed” method to be called and rebuild the menu:
‘Toggle the value of the checkbox via code, and have it trigger
‘a ribbon update to reflect the new value
‘Check value of checkbox and flip it
If ThisWorkbook.chkBox1 = True Then
ThisWorkbook.chkBox1 = False
ThisWorkbook.chkBox1 = True
‘Invalidate the control, forcing it to reload
The “CallControl” routine here is only to demonstrate the use of the property for toggling the value of the checkbox property when manually clicked.Â The “ToggleChkBox1″ routine demonstrates the ability to change the Ribbon display via VBA code alone.
If you are curious about the order that things fire, either step through the code, or put a “Stop” command at the beginning of the following routines:
That should give you a fairly good feel for the order things go in.Â