Modifying the Ribbon – Part 10

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

The purpose of this post is to demonstrate how to use the Ribbon to interact with our application. Specifically, this example has an edit box which will update with the worksheet name every time you change worksheets. In addition, if you change the name in the edit button, then press the "commit" button, it will change the name of the worksheet to your new selection. (Providing the new name is allowed, of course.)

XML Markup Required

Our XML for this example uses the onLoad assignment to capture our ribbonUI object, allowing us to force a rebuild of our ribbon items when needed, as well as the onAction, onChange and getText callbacks.
[xml]










[/xml]

VBA Code Required - ThisWorkbook Module

Again, we have the need to create workbook properties to set and retrieve the values of our ribbon controls. This time, we need to store the name in the editBox, which can be applied as a worksheet name, as well as the RibbonUI object. The editBox property is first used immediately upon opening the workbook, when we assign the active worksheet's name to it. It is then subsequently updated every time a new sheet is activated in the workbook. The code required in the ThisWorkbook module to accomplish this is shown below:

[vb]Option Explicit

'Private variables to hold state of Ribbon and Ribbon controls
Private pRibbonUI As IRibbonUI
Private sEditBox1Text As String

Public Property Let EditBox1Text(s As String)
'Store the button name
sEditBox1Text = s
End Property

Public Property Get EditBox1Text() As String
'Retrieve the button name
EditBox1Text = sEditBox1Text
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()
'Store the name of the active worksheet
ThisWorkbook.EditBox1Text = ActiveSheet.Name
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'Set the name of the worksheet to the text property
With ThisWorkbook
.EditBox1Text = ActiveSheet.Name
.ribbonUI.InvalidateControl editBox1Name
End With
End Sub
[/vb]

VBA Code Required - Standard Module

The following code contains all the callbacks necessary to:

• Store the ribbonUI object to the Workbook property (via the onLoad routine)
• Return the text for the editBox control when the RibbonX code requires it (rbnGetText routine)
• React to manual changes of the text values in the EditBox control (via the rbnEditBox1_Change routine)
• Respond to the button click requesting a worksheet name change (via the rbnButton_Click routine)

[vb]Option Explicit

Const Btn1Name = "button1"
Public Const editBox1Name = "editBox1"

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

Sub rbnGetText(control As IRibbonControl, ByRef returnedVal)
'Get the text value for the editBox
If control.ID = editBox1Name Then returnedVal = ThisWorkbook.EditBox1Text
End Sub

Private Sub rbnEditBox1_Change(control As IRibbonControl, text As String)
'Store the text value of the editBox for later use.
If control.ID = editBox1Name Then
ThisWorkbook.EditBox1Text = text
End If
End Sub

Private Sub rbnButton_Click(control As IRibbonControl)
'Rename the worksheet
Dim sNewSheetName As String
Dim ws As Worksheet

If control.ID = Btn1Name Then
'Retrieve intended sheet name
sNewSheetName = ThisWorkbook.EditBox1Text

'Check if name is nothing
If Len(sNewSheetName) = 0 Then
MsgBox "I need a name, please.", _
vbOKOnly + vbCritical, "No name entered"
Exit Sub
End If

'Check for sheet name in use
For Each ws In ThisWorkbook.Worksheets
If ws.Name = sNewSheetName Then
MsgBox "Sheet name already used." & vbNewLine & _
"Please pick another", vbOKOnly + vbCritical, _
"Name in use!"
Exit Sub
End If
Next ws

'Would not be here if name not okay so set it
ActiveSheet.Name = sNewSheetName
End If
End Sub
[/vb]

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.) On this tab you'll find a group that looks like this:

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 EditBox1Text property to the name of the active sheet in the workbook
• 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 "GetText" callback for the editBox is executed. This queries the property we just set, and adjusts the text in the editBox to the name of the active worksheet (which we just stored in the EditBox1Text property, as explained above. Because the button's properties are set in the XML, no additional callbacks are required to build the button.
Our Ribbon is now fully loaded, the editbox has a name in it, and the button is ready to be clicked. First though, try navigating to other sheets in the workbook. Every time you activate another worksheet in the workbook, you will trigger the following chain of events:

• The Workbook_SheetActivate routine is fired as the new worksheet is selected
• The name of the new sheet is sent to the EditBox1Text property and stored for later use (in the "sEditBox1Text" private variable)
• The editBox is invalidated, forcing the RibbonX code to be rebuilt
• During the rebuilding, the rbnGetText routine is called, which subsequently retrieves the worksheet name from the EditBox1Text property and builds it into the refreshed ribbon control

The only thing that looks any different now is that the sheet name will have changed in the edit box. You can click back and forth between sheets to watch it change, although that will probably get stale rather quickly. 😉
So now, try typing something in the editbox and clicking the "Commit Name" button. If you check the active worksheet's tab, you'll see that the name has been updated. This was accomplished by the following actions, triggered when you clicked the button:

• As control passes from the editBox field to the button, the rbnEditBox1_Change routine is fired, which sends the value of the editBox to the EditBox1Text property for later use
• Once the above routine is complete, the rbnButton_Click routine is fired
• Control id is checked to make sure that we are reacting to the correct button. (Not really needed here, but would be if we added another button which used the same onAction routine.)
• The editBox's value is assigned to a string for checking
• The string is evaluated to make sure it is not blank. If it is, we tell the user and exit the routine without changing the name
• The string is checked to make sure that it doesn't conflict with an existing sheet name. If it is, the user is informed and we exit the routine without making changes
• If the string passed the above tests, we change the worksheet name.

While I can't really think of a good reason that you would want to add this functionality to the ribbon, I hope that it does a decent job of explaining the "how" of ribbon control and application interaction. 🙂