Modifying the Ribbon – Part 9

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:

[xml]










[/xml]

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.

[vb]'Private variables to hold state of Ribbon and Ribbon controls
Private pRibbonUI As IRibbonUI
Private sBtn1Nm As String

Public Property Let Btn1Nm(s As String)
'Store the button name
sBtn1Nm = s
End Property

Public Property Get Btn1Nm() As String
'Retrieve the button name
Btn1Nm = sBtn1Nm
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

Private Sub Workbook_Open()
'Set the name of the original button
ThisWorkbook.Btn1Nm = "Set on Open"
End Sub
[/vb]

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.

[vb]

Const Btn1Name = "button1"
Const editBox1name = "editBox1"

Private Sub OnLoad(ribbon As IRibbonUI)
'Set the RibbonUI to a workbook property for later use
ThisWorkbook.ribbonUI = ribbon
End Sub

Private Sub EditBox1_Change(control As IRibbonControl, text As String)
'Change the name of the button by invalidating the control.
If control.ID = editBox1name Then
With ThisWorkbook
.Btn1Nm = text
.ribbonUI.InvalidateControl Btn1Name
End With
End If
End Sub

Private Sub btnControl(control As IRibbonControl)
'Give the user some kind of feedback that they clicked the button
MsgBox "You got me!"
End Sub

Private Sub getLabel(control As IRibbonControl, ByRef returnVal)
'Return the label for the selected control to the Ribbon
If control.ID = Btn1Name Then
returnVal = ThisWorkbook.Btn1Nm
End If
End Sub
[/vb]

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