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.

Essential Background
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:

[xml]

[/xml]

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
End Property

Public Property Get chkBox1() As Boolean
'Read value of chkBox1 property
chkBox1 = bChkBox1
End Property

Public Property Let ribbonUI(iRib As IRibbonUI)
'Set RibbonUI to property for later use
Set pRibbonUI = iRib
End Property

Public Property Get ribbonUI() As IRibbonUI
'Retrieve RibbonUI from property for use
Set ribbonUI = pRibbonUI
End Property[/vb]

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
End Sub[/vb]

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
Case True
MsgBox "The checkbox is checked!"
Case False
MsgBox "The checkbox is NOT checked!"
End Select
End Sub[/vb]

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
End Sub[/vb]

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
End Sub[/vb]

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:

[vb]Sub ToggleChkBox1()
'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
Else
ThisWorkbook.chkBox1 = True
End If

'Invalidate the control, forcing it to reload
ThisWorkbook.ribbonUI.InvalidateControl "chkbox1"
End Sub[/vb]

Final Notes

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:

• Workbook_Open
• OnLoad
• GetPressed

That should give you a fairly good feel for the order things go in.  🙂

Previous posts on this subject:

• -Part 1
• -Part 2
• -Part 3
• -Part 4

Creating a CheckBox

Until now, all the posts have focussed on creating buttons in a variety of styles, shapes and sizes. Today, we'll take a look at a simple example of a checkbox. What we'll do is create a single checkbox on a custom tab.

The XML to create a checkbox is once again similar to what we've seen. One big difference is that we cannot use a picture, of course, since the actual check box will take it's place. The XML for our test is as follows:

[xml]

[/xml]

This will create a custom tab called "XLG", and on it will be the lonely control as shown below:

Now, the checkbox is there, but we need to be able to figure out if it is clicked. If you recall Part 1 of this series, we set up a routine called "CallControl" (specified in our XML), that started like this:

[vb]Private Sub CallControl(Control As IRibbonControl)[/vb]

To determine the value of the checkbox, however, we need a slightly expanded VBA routine. We still use the basic framework as above, but we expand it to also pick up the "Pressed" portion as shown below:

[vb]Private Sub CallControl(control As IRibbonControl, pressed As Boolean)
'Tell the user the state of the control when they physically click it.

Select Case pressed
Case True
MsgBox "The checkbox is checked!"
Case False
MsgBox "The checkbox is NOT checked!"
End Select
End Sub

[/vb]

Again, the code above goes in a standard module. Once you have it there, try clicking the checkbox on the Ribbon, and notice that it will feed back if it is checked or not.

This example is educational only, of course. In a real setting you would replace the message boxes with code that you would wish to execute based on the user's selection.

In the next part, we will look at how to toggle the values of the checkbox via code, and have it update the Ribbon automatically... stay tuned! 🙂

Previous posts on this subject:

• -Part 1
• -Part 2
• -Part 3

Using Your Own Picture on a Button

Well... maybe not your own picture... unless you're feeling vain today. 😉 I really meant using an image of your choice. This is actually very easy, provided that you use the CustomUI Editor tool from OpenXML Developer that I blogged about in Part 1 of this series. Here's what you need to do...

First, open up your file in the CustomUI editor tool. Click the picture button (Insert Icons), browse to your picture and click OK. You'll now see your picture on the right side of the CustomUI editor, just like in the screen capture below:

If the image does not show up there, then you'll need to try again, as the pane does not show unless an image is attached.

Now, if you end up with a really long and weird looking file name, don't despair. You can rename the image to something more usable simply by right clicking it and choosing Change ID. Since the CustomUI editor replaces spaces in file names with the string "_x0020_", you may want to take advantage of this.

The next step is to edit your XML to refer to the custom picture. If you are using the "imageMso=" tags in your XML, change that to simply read "image=". So for the example above, you would enter image="logo". Again, remember that this will be case sensitive.

For the following example, I downloaded my website logo, imported it my file via the CustomUI editor, and used the following XML to create my menu:

[xml]

[/xml]

The result of this markup gave me a custom tab named "XLG", with the following on it:

A quick notes on custom images...

You can import more than one custom image into a file, but there seems to be a size limit to how many images you can store. I tried importing a 928KB image in addition to the 57KB logo file. The result was that neither button showed and image at all. I did import 3 images into a group, but again, I added a large image file and all the button images disappeared. Despite the fact that images are automatically scaled, I would suggest that full pictures are probably not the most appropriate images (even my website logo is too big.)

Here's the previous parts to this:

• -Part 1
• -Part 2

Using Excel's Images on Custom Ribbon Controls

In Part 2, we built the following custom group on a custom tab:

This time, we'll replace all the happy face images with pictures from Excel's native ribbon images. Once you know how, it's actually relatively easy to state which image you want. You just replace the HappyFace portion of the imageMso tag with the name of the Excel control that we want to use. The first question that we'll obviously need to settle, however, is "How do we get the name of the control?"

What you need to do is go into the Excel Options screen, (click the Office button, then Excel Options,) and click Customize. You'll now see the list of all the items that you can add to your Quick Access Toolbar (QAT). If you're a developer in prior versions of Excel, you'll remember that you pretty much had to create a custom toolbar to work out what FaceID corresponded to what picture. The good news here, is that things are much easier. 🙂

Move your mouse over one of the menu items and hover it there. A screen tip will pop up, and you'll find the MSO image name at the end of the string! A picture is shown below of the tool tip.

What I've done below is grab a few random imageMso's and put them into the XML for the example we created at the end of part 2. (Reproduced above.) The group now looks like this:

The images were selected based only on the fact that I thought they looked interesting. That's all. Remember that these images will only do what you program them too, as shown in Part 1 of this series.

The XML code to create this group is shown below. One final word of note here... the imageMso tag is again Case Sensitive! If you mess up the case, you get no image at all on your control. The ribbon does at least get created though, which is better than the case sensitivity issues that I told you about in Part 2.

[xml]

[/xml]

Part 1 of this series can be found here.

In this post, I'm going to show a few more examples of customizing the ribbon using buttons. (We'll get to other types of controls later.) All of these are variations of the schema set up in the first post linked to above.

Three Small Buttons to a Column

In the example from our previous section, we saw how to add three new buttons to a group in the ribbon. The following XML adds a forth button, (no major excitement there,) but it does show how to start a second column in the group. (Similar to text wrapping.)

[code]

[/code]

The end result looks like this:

Adding the Group to an Existing Tab

This time, we'll add the the group to an existing tab. We'll go back to the three button scenario, and put it right next to the clipboard on the Home tab. (I'm not suggesting you put your items there, as this is only an example.) So here's the code to do exactly that:

[code]

[/code]

Notice that the XML to create the tab only declares the tab idMso = "TabHome", rather than going through whole CustomTab setup portion. In addition, though we do need to specify where the new group will go, so we need to add the insertAfterMso="GroupClipboard"

And by the way, just to make this more fun, the XML markup is case sensitive. 🙁

The net effect of the above XML is the following:

Adding Big Buttons

Now we'll go back to the original example from the first post again. This time, however, we'll add another custom group, with three large buttons on it. (This is in addition to the original group.)

The code:

[code]

[/code]

The only real difference between the XML used to create the button is the inclusion of size="large" in the second group. Without that, the buttons end up being small and stacked on top of each other, as in our first example. With the size clause included, they are bigger and placed horizontally in the group, as shown below:

Mixing Button Sizes

The final example of this article will display mixing the large and small buttons. This time, all the buttons are on Custom Group 1 of the Custom Tab. I've mixed up order though, and dropped Button 3, just to show how the mixed sizes will display.

[code]

[/code]

The output from this XML is shown below: