Modifying the Ribbon – Part 6

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

Share:

Facebook
Twitter
LinkedIn

19 thoughts on “Modifying the Ribbon – Part 6”

  1. Ken,

    Thanks for posting an informative and easy to follow explanation of how these elements all tie together. This is the single-most helpful description I have seen of this process since I started down this path 3 months ago (and, trust me, I have seen a LOT of other attempts that all left me wondering about the "parts they didn't explain". Well done!

    Jeff

  2. Hi Ken,

    Hope you might still be monitoring comments here. As my last post stated, I thought your article here explained these issues very well, I have encountered a problem trying to adapt your methods to my own use. After adding all of your code to my VBAProject and changing my namespace declaration to match your:

    ... I modified one of my macros by adding the:

    getPressed="GetPressed"

    ... parameter to the XML statement for my callback procedure. When I attempt to validate same, though (in the CustomUI Editor), I am getting this error:

    "The 'getPressed' attribute is not declared."

    I've done quite a bit of searching around but couldn't find anything I could understand. I'm pretty new to XML so would highly appreciate any advice you could provide here.

    Thanks,

    Jeff

  3. Umm, my statement above that:

    "I modified one of my macros by adding the:

    getPressed="GetPressed"

    ... parameter to the XML statement for my callback procedure."

    ... may have been confusing. I didn't modify "my macro" rather it was the XML I changed.

    Jeff

  4. Ken,

    In reviewing the XML Schema for RibbonX - customUI.xsd, I belatedly discovered I was trying to use the getPressed attribute on a regular button (where it's not supported). Changing this to a toggleButton type resolved the error. I appreciate your follow-up email that also pointed me in this direction.

    Jeff

  5. I have incorporated this code into a workbook. When I try ToggleChkBox1 it goes to the InvalidateControl line, from there it goes to Get RibbonUI. When it then gets to End Property, I get an excel error message saying "Object variable or With block variable not set". Could you give me an idea as to what I might be doing incorrectly?

    Thanks

  6. Hi Bob,

    Maybe you could send me a copy of your workbook so I could take a quick look. My email address is ken@ this site.

    Cheers,

    Ken

  7. is there a way to reload ribbon object. i am using ms access and i store ribbon xml in the table. After i load the ribbon the first time, everything works fine,however, once i stumble upon a bug on the form, the ribbon doesn't work, because i have to invalidate a control and i get an error message stating "object variable or With block variable not set'...this happens on the line, where i try to invalidate the ribbon control. My guess this happens because the Ribbon object became null once the error occured, is there way to preserve the ribbon object or to reload it without restarting the whole application?

    Thanks
    Aleks

  8. Hi Aleks,

    Unfortunately, no there isn't. This underscores the importance of having VERY comprehensive error handling, so that you don't end up losing the Ribbon object.

    I'm really hoping that this gets changed in a future version, as having to re-open the file to reinstate the Ribbon is pretty ridiculous, IMO.

  9. Ken
    I got the same error as Bob Eastman. Did you find out why?

    Great example though, everything else works perfectly.

    Andy

  11. I am developing an add-in, but I have this problem. For the ribbon controls, I have callbacks getPressed, etc., but the VBA subs that handle these will not return the correct values unless Workbook_Open is run and many internal variables are set (this is a fairly complex program, so there is no other way).

    How do I make sure that these callbacks are not made when the add-in is loaded until after Workbook_Open is executed? Thanks.

  12. " * 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)"

    I wrote a test program and found that the reverse is true- that is, Workbook_Open is executed before anything else.

    Can you please confirm this one way or the other? Thanks.

  13. Ken - This was an amazing post, thank you! I recently had to install Excel 2010 from 2003, dark ages, I know. I was able to add multiple checkbox controls to a ribbon that I am developing based on the above post/examples...
    Can you provide an example of how to set the checkbox values onload from an existing VBA module instead of invalidating them?
    ex: when the ribbon loads, set the checkbox values based on a module that runs when the workbook opens.
    Gary

  14. Hi Gary,

    What I would do is set up the getPressed event as above, and in that event I would read from your module.

    The method that I use personally is to set up the workbook properties (as above), and write them into a CustomDocumentProperty. Every time the state of the control is change, I update the CustomDocumentProperty. This allows me to preserve the state between sessions when the workbook is in use.

  15. Hi Ken, Thank you for posting a reply so quickly.

    I am not familiar with CustomDocumentProperties and how they are able to be hooked into a persistant module that looks for the status to apply to a checkbox value. Or maybe I am not understanding how CDP work.

    Would you be able to post a simple example that would recogonize boolean from another Sub() Get_chkboxvalue?

    Gary

  18. Ken -
    I was able to "check" this out and it works perfectly! Thank you again.

    Like others in this blog I am not able to get the code posted for the "ToggleChkBox1" routine to execute as you describe. I as well get the error message stating "object variable or With block variable not set'

    Are you able to look into this code again and re-post?
    Gary

    1.Sub ToggleChkBox1()
    2.'Toggle the value of the checkbox via code, and have it trigger
    3.'a ribbon update to reflect the new value
    4.
    5.'Check value of checkbox and flip it
    6.If ThisWorkbook.chkBox1 = True Then
    7.ThisWorkbook.chkBox1 = False
    8.Else
    9.ThisWorkbook.chkBox1 = True
    10.End If
    11.
    12.'Invalidate the control, forcing it to reload
    13.ThisWorkbook.ribbonUI.InvalidateControl "chkbox1"
    14.End Sub

  19. Ken,
    After looking back at the posts I made. I have to thank you again. It has been a year and 4 months (to the day) since I have bothered you last. I am now getting asked to make my Excel Add-in backward compatible to those using Excel 2007. I thought that I was on track but the Add-in doesn't seem to work in 2007.
    Here is the xml:
    (2007 version)

    (2010 version) {works perfectly in as an Add-in for Excel 2010}

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

Copyright © 2022-present Excelguru Consulting Inc. All rights reserved.