Modifying the Ribbon – Part 9

Posted on December 8th, 2006 in Excel,Office 2007,The Ribbon by Ken Puls

The history of posts on the Ribbon can be found at the Ribbon Example Table of Contents.

In this article I'm going to demonstrate using an "edit box" to change the description of a button on the Ribbon.

XML Markup

Unlike in prior examples, the XML Markup we'll use here uses a callback to assign the button name, which allows it to be dynamic:

PLAIN TEXT
XML:
  1. <customUI onLoad="OnLoad" xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  2. <ribbon startFromScratch="false">
  3. <tabs>
  4. <tab id = "xlgTab" label="XLG" insertAfterMso="TabView">
  5. <group id="TestTab" label="My Testing Tab">
  6. <button id="button1" getLabel="GetLabel" onAction="btnControl" imageMso="HappyFace" />
  7. <editBox id="editBox1" label="New Button Name:" onChange="EditBox1_Change" />
  8. </group>
  9. </tab>
  10. </tabs>
  11. </ribbon>
  12. </customUI>

VBA Code Required - ThisWorkbook Module

Like the checkbox example in part 7, we'll need to add properties to the workbook. In this case, however, it will allow us to capture the name of the button, and also the RibbonUI object. Next, we'll need to ensure that the button is assigned a default name when we start the workbook. The code to accomplish this is shown below.

PLAIN TEXT
Visual Basic:
  1. 'Private variables to hold state of Ribbon and Ribbon controls
  2. Private pRibbonUI As IRibbonUI
  3. Private sBtn1Nm As String
  4.  
  5. Public Property Let Btn1Nm(s As String)
  6. 'Store the button name
  7. sBtn1Nm = s
  8. End Property
  9.  
  10. Public Property Get Btn1Nm() As String
  11. 'Retrieve the button name
  12. Btn1Nm = sBtn1Nm
  13. End Property
  14.  
  15. Public Property Let ribbonUI(iRib As IRibbonUI)
  16. 'Set RibbonUI to property for later use
  17. Set pRibbonUI = iRib
  18. End Property
  19.  
  20. Public Property Get ribbonUI() As IRibbonUI
  21. 'Retrieve RibbonUI from property for use
  22. Set ribbonUI = pRibbonUI
  23. End Property
  24.  
  25. Private Sub Workbook_Open()
  26. 'Set the name of the original button
  27. ThisWorkbook.Btn1Nm = "Set on Open"
  28. End Sub

VBA Code Required - Standard Module

Like in part 7, I again chose to assign the button and editbox name to constants. The remaining code required will:

  • Use the OnLoad routine (with the same name as specified in our XML) to capture the RibbonUI object to a workbook property
  • Set up the GetLabel callback which will return the name of our button
  • Set up routines (again as specified in our XML) to react to the changing of the editbox value and the button clicks.
PLAIN TEXT
Visual Basic:
  1. Const Btn1Name = "button1"
  2. Const editBox1name = "editBox1"
  3.  
  4. Private Sub OnLoad(ribbon As IRibbonUI)
  5. 'Set the RibbonUI to a workbook property for later use
  6. ThisWorkbook.ribbonUI = ribbon
  7. End Sub
  8.  
  9. Private Sub EditBox1_Change(control As IRibbonControl, text As String)
  10. 'Change the name of the button by invalidating the control.
  11. If control.ID = editBox1name Then
  12. With ThisWorkbook
  13. .Btn1Nm = text
  14. .ribbonUI.InvalidateControl Btn1Name
  15. End With
  16. End If
  17. End Sub
  18.  
  19. Private Sub btnControl(control As IRibbonControl)
  20. 'Give the user some kind of feedback that they clicked the button
  21. MsgBox "You got me!"
  22. End Sub
  23.  
  24. Private Sub getLabel(control As IRibbonControl, ByRef returnVal)
  25. 'Return the label for the selected control to the Ribbon
  26. If control.ID = Btn1Name Then
  27. returnVal = ThisWorkbook.Btn1Nm
  28. End If
  29. End Sub

The End Result

Once you've saved all the code, close and re-open the workbook. You'll have a custom XLG tab (that you've become used to if you're following the series,) which has a button bearing the description "Set on Open", and an editBox showing "New Button Name:"
The code fires in this order when the workbook is opened up (assuming Macros are enabled):

  • The RibbonX code is loaded, and the XLG tab is added
  • The Workbook_Open event is fired, setting the values of the button name to "Set on Open"
  • The OnLoad procedure runs, capturing the RibbonUI object to a property for later use in triggering callbacks

At this point, you have the XLG tab showing on the Ribbon. When you click that tab, the "GetLabel" callbacks for the button is executed. This queries the property we just set, and adjusts the button name to that value.
Our Ribbon is now fully loaded, the editbox is clear, and the button bears its default caption. Typing something in the editbox and hitting enter will set off the following chain of events:

  • The EditBox1_Change routine is fired
  • The name in the editbox is assigned to the button name property
  • The button control is invalidated, forcing the RibbonX code to be rebuilt by calling the GetLabel routine

The end result is that the value in the editBox is sent back to the button, and the name of the button changes to what you just submitted. :)

6 Comments

6 Responses to 'Modifying the Ribbon – Part 9'

Subscribe to comments with RSS or TrackBack to 'Modifying the Ribbon – Part 9'.

  1. Chris Dunsford said,

    on March 20th, 2007 at 3:52 am

    great article
    however, is it possible to get the editbox text without hitting enter?
    for example, i have an editbox that a user can type in search criteria. there is also a search button. once the user enters search text, he wants to be able to click the search btn.but this action doesn't fire a change event. any ideas?

  2. Ken Puls said,

    on March 20th, 2007 at 8:48 pm

    Hi Chris,

    Sorry, but I don't know what to tell you here. Using the example code in this post, I was able to type text in the edit box, not hit enter, and when I click the button it renames it. I can't see why it wouldn't work for your purpose.

  3. Aditya said,

    on June 20th, 2007 at 11:03 pm

    I have a question otherway round.
    Is it possible to track the keypress and act accordingly. I want the same functionality as Chris mentioned. But in my case I would like to give the functionality of type text and press 'enter' to search. Is that possible? Some pointers if it is.

    Thanks.

  4. Ken Puls said,

    on June 22nd, 2007 at 7:48 am

    Hi Aditya,

    Unfortunately, it doesn't seem possible at this point. Where the VBA userform's onChange event fired each time you entered a new character, the editBox's callback is only fired when you the control loses focus (by clicking elsewhere or pressing Enter.)

    The only way I can think of integrating a full "hot" searchbox into the new UI is to create a custom Taskpane to do so. Unfortunately, since we can't create taskpanes within the Office apps themselves, this involves using Visual Stuio, and probably VSTO. I haven't tried it myself, though. so can't be 100% sure on this.

  5. Sushil said,

    on January 9th, 2008 at 11:02 pm

    Hi,

    How we can enter password characters in editbox or is there any other control which we can enter password character in control in ribbon

    Thanks,
    Sushil

  6. Ken Puls said,

    on February 14th, 2008 at 1:45 pm

    Hi Sushil,

    Unfortunately there is no facility to do that at the moment. Hopefully we'll see it in the next version.

Post a comment