UEU-co logo

Customizing the Ribbon


Previous Page

Next Page

Customizing the Ribbon

You can’t perform any Ribbon modifications using VBA. Rather, you must write RibbonX code and insert the code into the workbook file – which is done outside of Excel. You can, however, create VBA callback procedures. A callback procedure is a VBA macro that is executed when a custom Ribbon control is activated.

RibbonX code is XML markup that describes the controls, where in the Ribbon they are displayed, what they look like, and what happens when they are activated. This book does not cover RibbonX – it’s complex enough to be the subject of an entire book. I do, however, provide a few simple examples so you can understand what’s involved in modifying the Excel 2007 UI and decide if it’s something you’d like to learn.

A simple RibbonX example

This section contains a step-by-step walkthrough that will give you a feel for what it takes to modify Excel’s Ribbon. This example creates a new Ribbon group (named Custom) on the Data tab. It also creates two buttons in the new Ribbon group, labeled Hello World and Goodbye World. Clicking either of these buttons executes a VBA macro.

Image from book

See Your Errors

Before you do any work with Ribbon customization, you should enable the display of RibbonX errors. Access the Office Image from book Excel Options dialog box and click the Advanced tab. Scroll down to the General section and place a check mark next to Show Add-in User Interface Errors.

When this setting is enabled, RibbonX errors (if any) are displayed when the workbook opens – which is very helpful for debugging.

Image from book

Follow these steps to create a workbook that contains RibbonX code that modifies the Ribbon:

  1. Create a new Excel workbook, insert a VBA module, and enter two callback procedures. These are the procedures that are executed when the buttons are clicked:

    Sub HelloWorld(control As IRibbonControl) MsgBox “Hello World!” End Sub Sub GoodbyeWorld(control As IRibbonControl) ThisWorkbook.Close End Sub

  2. Save the workbook and name it Image from book ribbon modification.xlsm.

  3. Close the workbook.

  4. Activate the folder that contains the Image from book ribbon modification.xlsm file and create a folder named customUI.

  5. Inside that folder, use a text editor (such as Windows Notepad) to create a text file named customUI.xml with the following RibbonX XML code:

    <customUI xmlns=”http://schemas.microsoft.com/office/2006/01/customui”> <ribbon> <tabs> <tab idMso=”TabData”> <group id=”Group1″ label=”Custom”> <button id=”Button1″ label=”Hello World” size=”normal” onAction=”HelloWorld” imageMso=”HappyFace” /> <button id=”Button2″ label=”Goodbye World” size=”normal” onAction=”GoodbyeWorld” imageMso=”DeclineInvitation” /> </group> </tab> </tabs> </ribbon> </customUI>

  6. Using Windows Explorer, add a .zip extension to the Image from book ribbon modification.xlsm file in Windows Explorer. The filename should now be ribbon modification.xlsm.zip.

  7. Drag the customUI folder you created in Step 4 into the ribbon modification.xlsm.zip file. Windows treats ZIP files as if they were folders, so drag-and-drop operations are allowed.

  8. Double-click the ribbon modification.xlsm.zip file to open it. Figure 22-4 shows the contents of the ZIP file. As you see, the file contains several folders.

    Image from book
    Figure 22-4: An Excel workbook, displayed as a ZIP file.

  9. Double-click the _rels folder within the ZIP file. This folder contains one file, named .rels.

  10. Drag the .rels file to a location outside the ZIP file (to your Desktop, for example).

  11. Open the .rels file (which is an XML file) with a text editor, such as Notepad.

  12. Add the following line to the .rels file, before the </Relationships> tag:

    <Relationship Type=”http://schemas.microsoft.com/office/2006/relationships/ui/ extensibility” Target=”/customUI/customUI.xml” Id=”12345″ />

  13. Save the .rels file and drag it back into the ZIP file, overwriting the original version.

  14. Remove the .zip extension so that the file is back to its original name: Image from book ribbon modification.xlsm.

Open the workbook in Excel. If all went well, you should see a new group with two buttons in the Data tab (see Figure 22-5).

Image from book
Figure 22-5: RibbonX code created a new group with two buttons.

CD-ROM 

This workbook, named Image from book ribbon modification.xlsm, is available on the companion CD-ROM.

It’s important to understand that the Ribbon modification is document-specific. In other words, the new Ribbon group is displayed only when the workbook that contains the RibbonX code is the active workbook. This is a major departure from how UI modifications worked in previous versions of Excel.

Tip 

To display Ribbon customizations when any workbook is active, convert the workbook to an add-in file, or add the RibbonX code to your Personal Macro Workbook.

If you’ve concluded that modifying Excel’s Ribbon is not worth the effort, don’t despair. Tools will be available that make the process much less tedious than I’ve described. As I write this book, only one such tool is available: Office 2007 Custom UI Editor, written by Trang Luu (see Figure 22-6). This program still requires that you create the RibbonX code manually, but it will validate the code for you. It also eliminates all the tedious manual file manipulations. And finally, it can generate the VBA callback procedure declarations, which you can copy and paste to your VBA module.

Image from book
Figure 22-6: The Office 2007 Custom UI Editor.

More about the simple RibbonX example

This section provides some additional details about the Image from book ribbon modification.xlsm workbook I discuss in the previous section.

VBA CALLBACK PROCEDURES

Recall that the workbook contains two VBA procedures, HelloWorld and GoodbyeWorld. These procedure names correspond to the onAction parameters in the RibbonX code. The onAction parameter is one way to link the RibbonX code to your VBA code.

Both the VBA procedures contain an argument named control, which is an IRibbonControl object. This object has three properties, which you can access in your VBA code:

  • Context: A handle to the active window containing the Ribbon that triggered the call-back. For example, use the following expression to get the name of the workbook that contains the RibbonX code:

    control.Context.Caption

  • Id: Contains the name of the control, specified as its Id parameter.

  • Tag: Contains any arbitrary text that’s associated with the control.

The VBA callback procedures can be as complex as necessary.

THE .RELS FILE

Inserting the file that contains the RibbonX code has no effect unless you specify a relationship between the document file and the customization file. These relationships, written in XML, are stored in the .rels file, which is in the _rels folder. Here’s the relationship for the example presented in the previous section:

<Relationship Type=”http://schemas.microsoft.com/office/2006/ relationships/ui/extensibility” Target=”/customUI/customUI.xml” Id=”12345″ />

The Target parameter points to the customUI.xml file that contains the RibbonX code. The Id parameter contains an arbitrary text string. The string can contain anything, as long as it’s unique to the file (that is, as long as no other <Relationship> tag uses the same Id).

THE RIBBONX CODE

And now, the tricky part. Writing the XML code that defines your UI modification is no easy task. As I’ve noted, this is not the book that will teach you how to write RibbonX code. You’ll find a few simple examples here, but you’ll need to consult other sources for the fine points.

Image from book

Using imageMso Images

Microsoft Office 2007 provides more than 2,500 named images that are associated with various commands. You can specify any of these images for your custom Ribbon controls – if you know the image’s name.

The accompanying figure shows a workbook that contains the names of all the imageMso images. Scroll through the image names, and you see 50 images at a time (in small or large size), beginning with the image name in the active cell. This workbook, named Image from book mso image browser.xlsm, is available on the companion CD-ROM.

Image from book

You can also use these images in an Image control placed on a UserForm. The statement below assigns the imageMso image named ReviewAcceptChanges to the Picture property of a UserForm Image control named Image1. The size of the image is specified as 32 × 32 pixels.

Image1.Picture = Application.CommandBars. _ GetImageMso(“ReviewAcceptChange”, 32, 32)

Image from book

When you’re starting out, it’s best to start with examples that work (search the Web) and then make small modifications, testing frequently along the way. It can be very frustrating to spend an hour working on code that appears to be perfect in every way – and then realize that XML is case-sensitive. ID is not the same as Id.

Note 

You may be curious about the imageMso parameter, which determines which icon is displayed next to the control. Microsoft Office includes more than 2,500 icons that you can use with Ribbon controls. Each is accessed by its name. For more information, see the sidebar “Using imageMso Images.”

Another RibbonX example

This section contains another example of using RibbonX to modify the UI. This workbook creates a new group on the Page Layout tab and adds a check box control that toggles the display of page breaks.

Note 

Although Excel has more than 1,700 commands, it does not have a command that toggles the page break display. After printing or previewing a worksheet, the only way to hide the page break display is to use the Excel Options dialog box. Therefore, this example also has some practical value.

This example is a bit tricky because it requires that the new Ribbon control be in synch with the active sheet. For example, if you activate a worksheet that doesn’t display page breaks, the check box control should be in its unchecked state. If you activate a worksheet that displays page breaks, the control should be checked. Furthermore, page breaks aren’t relevant for a chart sheet, so the control should be disabled if you activate a chart sheet.

THE RIBBONX CODE

The RibbonX code that adds a new group (with a CheckBox control) to the Page Layout tab follows:

<customUI xmlns=”http://schemas.microsoft.com/office/2006/01/customui” onLoad=”Initialize”> <ribbon> <tabs> <tab idMso=”TabPageLayoutExcel”> <group id=”Group1″ label=”Custom”> <checkBox id=”Checkbox1″ label=”Page Breaks” onAction=”TogglePageBreakDisplay” getPressed=”GetPressed” getEnabled=”GetEnabled”/> </group> </tab> </tabs> </ribbon> </customUI>

This RibbonX code references four VBA callback procedures (each of which is described later):

  • Initialize: Executed when the workbook is opened.

  • TogglePageBreakDisplay: Executed when the user clicks the check box control.

  • GetPressed: Executed when the control is invalidated (the user activates a different sheet).

  • GetEnabled: Executed when the control is invalidated (the user activates a different sheet).

Figure 22-7 shows the new control.

Image from book
Figure 22-7: This check box control is always in synch with the page break display of the active sheet.

THE VBA CODE

The CustomUI tag includes an onLoad parameter, which specifies the Initialize VBA callback procedure, as follows:

Public MyRibbon As IRibbonUI Sub Initialize(Ribbon As IRibbonUI) ‘ Executed when the workbook loads Set MyRibbon = Ribbon End Sub

The Initialize procedure creates an IRibbonUI object named MyRibbon. Notice that MyRibbon is a Public variable, so it’s accessible from other procedures in the module.

I created a simple event-procedure that is executed whenever a worksheet is activated. This procedure, which is located in the ThisWorkbook code module, calls the CheckPageBreakDisplay procedure:

Private Sub Workbook_SheetActivate(ByVal Sh As Object) Call CheckPageBreakDisplay End Sub

The CheckPageBreakDisplay procedure invalidates the check box control. In other words, it destroys any data associated with that control.

Sub CheckPageBreakDisplay() ‘ Executed when a sheet is activated MyRibbon.InvalidateControl (“Checkbox1”) End Sub

When a control is invalidated, the GetPressed and GetEnabled procedures are called.

Sub GetPressed(control As IRibbonControl, ByRef returnedVal) ‘ Executed when the control is invalidated On Error Resume Next returnedVal = ActiveSheet.DisplayPageBreaks End Sub Sub GetEnabled(control As IRibbonControl, ByRef returnedVal) ‘ Executed when the control is invalidated returnedVal = TypeName(ActiveSheet) = “Worksheet” End Sub

Notice that the returnedVal argument is passed ByRef. This means that your code is able to change the value. And that’s exactly what happens. In the GetPressed procedure, the returnedVal variable is set to the status of the DisplayPageBreaks property of the active sheet. The result is that the control’s Pressed parameter is True if page breaks are displayed (and the control is checked). Otherwise, the control is not checked.

In the GetEnabled procedure, the returnedVal variable is set to True if the active sheet is a worksheet (as opposed to a chart sheet). Therefore, the control is enabled only when the active sheet is a worksheet.

The only other VBA procedure is the onAction procedure, TogglePageBreakDisplay, which is executed when the user checks or unchecks the check box:

Sub TogglePageBreakDisplay(control As IRibbonControl, pressed As Boolean) ‘ Executed when check box is clicked On Error Resume Next ActiveSheet.DisplayPageBreaks = pressed End Sub

This pressed argument is True if the user checks the check box and False if he unchecks the check box. The code sets the DisplayPageBreaks property accordingly.

CD-ROM 

This workbook, named Image from book page break display.xlsm, is available on the companion CD-ROM. The CD also contains an add-in version of this workbook (named Image from book page break display add-in.xlam), which makes the new UI command available for all workbooks. The add-in version uses a class module to monitor sheet activation events for all workbooks. Refer to Chapter 19 for more information about events, and Chapter 29 for more information about class modules.

Ribbon controls demo

Figure 22-8 shows a custom Ribbon tab (My Stuff) with four groups of controls. In this section, I briefly describe the RibbonX code and the VBA callback procedures.

Image from book
Figure 22-8: A new Ribbon tab with four groups of controls.

CD-ROM 

This workbook, named Image from book ribbon controls demo.xlsm, is available on the companion CD-ROM.

CREATING A NEW TAB

The RibbonX code that creates the new tab is

<ribbon> <tabs> <tab id=”CustomTab” label=”My Stuff”> </tabs> </ribbon>

Tip 

If you’d like to create a minimal UI, the ribbon tag has a startFromScratch attribute. If set to True, all the built-in tabs are hidden. In addition, all the Office button menu commands are hidden except for New, Open, Excel Options, and Exit.

<ribbon startFromScratch=”true” >

CREATING A RIBBON GROUP

The code in the Image from book ribbon controls demo.xlsm example creates four groups on the My Stuff tab. Here’s the code that creates the four groups:

<group id=”Group1″ label=”Stuff”> </group> <group id=”Group2″ label=”More Stuff”> </group> <group id=”Group3″ label=”Built In Stuff”> </group> <group id=”Group4″ label=”Galleries”> </group>

Theses pairs of <group> and </group> tags are located within the <tab> and </tab> tags that create the new tab.

CREATING CONTROLS

Following is the RibbonX code that creates the controls in the first group (Stuff), shown in Figure 22-9. Notice that the controls are defined within the first set of <group> </group> tags.

Image from book
Figure 22-9: A Ribbon group with four controls. <group id=”Group1″ label=”Stuff”> <labelControl id=”Label1″ getLabel=”getLabel1″ /> <labelControl id=”Label2″ getLabel=”getLabel2″ /> <editBox id=”EditBox1″ showLabel=”true” label=”Number:” onChange=”EditBox1_Change”/> <button id=”Button1″ label=”Calculator” size=”large” onAction=”ShowCalculator” imageMso=”Calculator” /> </group>

Two label controls each have an associated VBA callback procedure (named getLabel1 and getLabel2). These procedures are:

Sub getLabel1(control As IRibbonControl, ByRef returnedVal) returnedVal = “Hello ” & Application.UserName End Sub Sub getLabel2(control As IRibbonControl, ByRef returnedVal) returnedVal = “Today is ” & Date End Sub

When the RibbonX code is loaded, these two procedures are executed, and the captions of the label controls are dynamically updated with the username and the date.

The editBox control has an onChange callback procedure named EditBox1_Change, which displays the square root of the number entered (or an error message if the square root can’t be calculated). The EditBox1_Change procedure is

Sub EditBox1_Change(control As IRibbonControl, text As String) Dim squareRoot As Double On Error Resume Next squareRoot = Sqr(text) If Err.Number = 0 Then MsgBox “The square root of ” & text & ” is: ” & squareRoot Else MsgBox “Enter a positive number.”, vbCritical End If End Sub

The last control in the Stuff group is a simple button. It’s onAction parameter executes a VBA procedure named ShowCalculator – which uses the VBA Shell function to display the Windows calculator:

Sub ShowCalculator(control As IRibbonControl) On Error Resume Next Shell “calc.exe”, vbNormalFocus If Err.Number <> 0 Then MsgBox “Can’t start calc.exe” End Sub

Figure 22-10 shows the controls in the second group, labeled More Stuff.

Image from book
Figure 22-10: Three controls in a custom Ribbon group.

The RibbonX code for the second group is as follows:

<group id=”Group2″ label=”More Stuff”> <toggleButton id=”ToggleButton1″ size=”large” imageMso=”FileManageMenu” label=”Toggle Me” onAction=”ToggleButton1_Click” /> <separator id=”sep1″ /> <checkBox id=”Checkbox1″ label=”Checkbox” onAction=”Checkbox1_Change”/> <comboBox id=”Combo1″ label=”Month” onChange=”Combo1_Change”> <item id=”Month1″ label=”January” /> <item id=”Month2″ label=”February”/> <item id=”Month3″ label=”March”/> <item id=”Month4″ label=”April”/> <item id=”Month5″ label=”May”/> <item id=”Month6″ label=”June”/> <item id=”Month7″ label=”July”/> <item id=”Month8″ label=”August”/> <item id=”Month9″ label=”September”/> <item id=”Month10″ label=”October”/> <item id=”Month11″ label=”November”/> <item id=”Month12″ label=”December”/> </comboBox> </group>

The group contains a toggleButton, a separator, a checkBox, and a comboBox control. These controls are fairly straightforward. Except for the separator control (which inserts a vertical line), each has an associated callback procedure that simply displays the status of the control:

Sub ToggleButton1_Click(control As IRibbonControl, ByRef returnedVal) MsgBox “Toggle value: ” & returnedVal End Sub Sub Checkbox1_Change(control As IRibbonControl, pressed As Boolean) MsgBox “Checkbox value: ” & pressed End Sub Sub Combo1_Change(control As IRibbonControl, text As String) MsgBox text End Sub

Note 

The comboBox control also accepts user-entered text. If you would like to limit the choices to those that you provide, use a dropDown control.

The controls in the third group consist of built-in controls (see Figure 22-11). To include a built-in control in a custom group, you just need to know its name (the idMso parameter).

Image from book
Figure 22-11: This group contains built-in controls.

The RibbonX code is

<group id=”Group3″ label=”Built In Stuff”> <control idMso=”Copy” label=”Copy” /> <control idMso=”Paste” label=”Paste” enabled=”true” /> <control idMso=”WindowSwitchWindowsMenuExcel” label=”Switch Window” /> <control idMso=”Italic” /> <control idMso=”Bold” /> <control idMso=”FileOpen” /> </group>

These controls don’t have callback procedures because they perform the standard action.

Figure 22-12 shows the final group of controls, which consists of two galleries.

Image from book
Figure 22-12: This Ribbon group contains two galleries.

The RibbonX code for these two gallery controls is

<group id=”Group4″ label=”Galleries”> <gallery id=”Gallery1″ imageMso=”ViewAppointmentInCalendar” label=”Pick a Month:” columns=”2″ rows=”6″ onAction=”MonthSelected” > <item id=”January” label=”January” imageMso=”QuerySelectQueryType”/> <item id=”February” label=”February” imageMso=”QuerySelectQueryType”/> <item id=”March” label=”March” imageMso=”QuerySelectQueryType”/> <item id=”April” label=”April” imageMso=”QuerySelectQueryType”/> <item id=”May” label=”May” imageMso=”QuerySelectQueryType”/> <item id=”June” label=”June” imageMso=”QuerySelectQueryType”/> <item id=”July” label=”July” imageMso=”QuerySelectQueryType”/> <item id=”August” label=”August” imageMso=”QuerySelectQueryType”/> <item id=”September” label=”September” imageMso=”QuerySelectQueryType”/> <item id=”October” label=”October” imageMso=”QuerySelectQueryType”/> <item id=”November” label=”November” imageMso=”QuerySelectQueryType”/> <item id=”December” label=”December” imageMso=”QuerySelectQueryType”/> <button id=”Today” label=”Today…” imageMso=”ViewAppointmentInCalendar” onAction=”ShowToday”/> </gallery> <gallery id=”Gallery2″ label=”Banjo Players” size=”large” columns=”4″ itemWidth=”100″ itemHeight=”125″ imageMso= “Camera” onAction=”OnAction”> <item id=”bp01″ image=”bp01″ /> <item id=”bp02″ image=”bp02″ /> <item id=”bp03″ image=”bp03″ /> <item id=”bp04″ image=”bp04″ /> <item id=”bp05″ image=”bp05″ /> <item id=”bp06″ image=”bp06″ /> <item id=”bp07″ image=”bp07″ /> <item id=”bp08″ image=”bp08″ /> <item id=”bp09″ image=”bp09″ /> <item id=”bp10″ image=”bp10″ /> <item id=”bp11″ image=”bp11″ /> <item id=”bp12″ image=”bp12″ /> <item id=”bp13″ image=”bp13″ /> <item id=”bp14″ image=”bp14″ /> <item id=”bp15″ image=”bp15″ /> </gallery> </group>

Figure 22-13 shows the first gallery, a list of month names in two columns. The onAction parameter executes the MonthSelected callback procedure, which displays the selected month (which is stored as the id parameter):

Image from book
Figure 22-13: A gallery that displays month names, plus a button. Sub MonthSelected(control As IRibbonControl, _ id As String, index As Integer) MsgBox “You selected ” & id End Sub

The Pick a Month gallery also contains a button control with its own callback procedure (labeled Today) at the bottom:

Sub ShowToday(control As IRibbonControl) MsgBox “Today is ” & Date End Sub

The second gallery, shown in Figure 22-14, displays 15 photos.

Image from book
Figure 22-14: A gallery of photos.

These photos are stored in the workbook file, in a folder named images, within the customUI folder. Adding images also requires a _rels folder, with a list of relationships. To see how this works, add a .zip extension to the workbook and then examine its contents.

A DynamicMenu Control Example

One of the most interesting Ribbon controls is the dynamicMenu control. This control lets your VBA code feed XML data into the control – which provides the basis for menus that change based on context.

Setting up a dynamicMenu control is not a simple task, but this control probably offers the most flexibility in terms of using VBA to modify the Ribbon dynamically.

I created a simple dynamicMenu control demo that displays a different menu for each of the three worksheets in a workbook. Figure 22-15 shows the menu that appears when Sheet1 is active. When a sheet is activated, a VBA procedure sends XML code specific for the sheet. For this demo, I stored the XML code directly in the worksheets to make it easier to read. Alternatively, the XML markup can be stored as a string variable in your code.

Image from book
Figure 22-15: The dynamicMenu control lets you create a menu that varies depending on the context.

The RibbonX code that creates the new tab, the new group, and the dynamicMenu control follows:

<customUI xmlns=”http://schemas.microsoft.com/office/2006/01/customui” onLoad=”ribbonLoaded”> <ribbon> <tabs> <tab id=”CustomTab” label=”Dynamic”> <group id=”group1″ label=”Dynamic Menu Demo”> <dynamicMenu id=”DynamicMenu” getContent=”dynamicMenuContent” imageMso=”RegionLayoutMenu” size = “large” label=”Sheet-Specific Menu”/> </group> </tab> </tabs> </ribbon> </customUI>

This example needs a way to invalidate the Ribbon whenever the user activates a new sheet. I use the same method I used for the page break display example earlier in this chapter (see “Another RibbonX example“): I declared a Public variable, MyRibbon, of type IRibbonUI. I used a Workbook_SheetActivate procedure that called the UpdateDynamicRibbon procedure whenever a new sheet is activated:

Sub UpdateDynamicRibbon() ‘ Invalidate the Ribbon to force a call to dynamicMenuContent On Error Resume Next MyRibbon.Invalidate If Err.Number <> 0 Then MsgBox “Lost the Ribbon object. Save and reload.” End If End Sub

The UpdateDynamicRibbon procedure invalidates the MyRibbon object, which forces a call to the VBA callback procedure named dynamicMenuContent (a procedure referenced by the getContent parameter in the RibbonX code). Notice the error-handling code. Some edits to your VBA code destroy the MyRibbon object, which is created when the workbook is opened. Attempting to invalidate an object that doesn’t exist causes an error, and the message box informs the user that the workbook must be saved and reopened. Unfortunately, reopening the workbook is the only way to re-create the MyRibbon object.

The dynamicMenuContent procedure follows. This procedure loops through the cells in column A of the active sheet, reads the XML code, and stores it in a variable named XMLcode. When all the XML has been appended, it’s passed to the returnedVal argument. The net effect is that the dynamicMenu control has new code, so it displays a different set of menu options.

Sub dynamicMenuContent(control As IRibbonControl, _ ByRef returnedVal) Dim r As Long Dim XMLcode As String ‘ Read the XML markup from the active sheet For r = 1 To Application.CountA(Range(“A:A”)) XMLcode = XMLcode & ActiveSheet.Cells(r, 1) & ” ” Next r returnedVal = XMLcode End Sub

CD-ROM 

The workbook that contains this example is available on the companion CD-ROM. The filename is Image from book dynamicmenu.xlsm.

More on Ribbon customization

I conclude this section with some additional points to keep in mind as you explore the wonderful world of Excel Ribbon customization:

Previous Page

Next Page

Leave a Reply


Time limit is exhausted. Please reload the CAPTCHA.

Categories

apply_now Pepperstone Group Limited