The Ken Puls Blog

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.

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:

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)

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.