Modifying the Ribbon – Part 5

Previous posts on this subject:

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:



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


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! 🙂



5 thoughts on “Modifying the Ribbon – Part 5

  1. Hi,

    I have created a toolbar on Excel 2007. I have set of toggleButtons on the toolbar.

    Im wondering, if i could set anyone of the toggleButton "pressed" when an excel file is opened.

    when I open an excel file, toggleButtons on my custom toolbar on by default apprear "unpressed". Here's my requirement, is there any procedure to set these toggleButtons to "pressed" by default".


  2. Hi Shiva,

    The way I would handle it is:
    -Add a global (boolean) variable called something like "btglbtnPressed" to your project
    -Use the Ribbon_OnLoad event to test if the toggleButton should be selected
    -If so, set btglbtnPressed=True
    -Modify the toggleButton's callback to read/record it's value from/to the btglbtnPressed variable

    This way, the toggleButton value will be set when the ribbon loads, and then be called each time you invalidate the ribbon.

  3. Dear Ken, I have found your blog full of knowledge and your illustration simple and easy to understand. I would like to request you to have a look at my problem and advise me the solution. Problem: I have created a custom tab with custom ui editor to be used in MS Excel(2007 and later). I have a checkbox control with the tab and users click on the checkbox for checking and unchecking. I want to run macros by clicking another custom button. But if the checkbox is checked ,the button will call one macro, if not, it will call another. How can i do that with vba in excel?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Latest Posts