Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

2017-07-07 02:10:07

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.

See Your Errors

Before you do any work with Ribbon customization, you should enable the display of RibbonX errors. Access the Office 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.

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

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

Save the workbook and name it ribbon modification.xlsm .

Close the workbook.

Activate the folder that contains the ribbon modification.xlsm file and create a folder named customUI.

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>

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

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.

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.

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

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

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

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

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" />

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

Remove the .zip extension so that the file is back to its original name: 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).

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

CD-ROM

This workbook, named 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.

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

More about the simple RibbonX example

This section provides some additional details about the 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:

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.

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 mso image browser.xlsm , is available on the companion CD-ROM.

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)

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 :

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.

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 page break display.xlsm , is available on the companion CD-ROM. The CD also contains an add-in version of this workbook (named 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.

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

CD-ROM

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

CREATING A NEW TAB

The RibbonX code that creates the new tab is

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 ribbon controls demo.xlsm example creates four groups on the My Stuff tab. Here's the code that creates the four groups:

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.

Figure 22-9: A Ribbon group with four controls.

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.

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

The RibbonX code for the second group is as follows:

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

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

The RibbonX code is

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.

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

The RibbonX code for these two gallery controls is

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):

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.

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.

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:

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 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:

When you're working with the Ribbon, make sure that you turn on error message display. Refer to the "See Your Errors" sidebar, earlier in this chapter.

Remember that RibbonX code is case-sensitive.

All the named control IDs are in English, and they are the same across all language versions of Excel. Therefore, Ribbon modifications work regardless of what language version of Excel is used.

Ribbon modifications appear only when the workbook that contains the RibbonX code is active. To make Ribbon modifications appear for all workbooks, the RibbonX code must be in an add-in.

The built-in controls scale themselves when the Excel window is resized. Custom controls are always the same size; they don't scale.

Adding or removing controls from a built-in Ribbon group is not possible.

You can, however, hide tabs. The RibbonX code that follows hides three tabs:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon> <tabs> <tab idMso="TabPageLayoutExcel" visible="false" /> <tab idMso="TabData" visible="false" /> <tab idMso="TabReview" visible="false" /> </tabs> </ribbon> </customUI>

You can also hide groups within a tab. Here's RibbonX code that hides four groups on the Insert tab (leaving only the Charts group):

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <ribbon> <tabs> <tab idMso="TabInsert"> <group idMso="GroupInsertTablesExcel" visible="false" /> <group idMso="GroupInsertIllustrations" visible="false" /> <group idMso="GroupInsertLinks" visible="false" /> <group idMso="GroupInsertText" visible="false" /> </tab> </tabs> </ribbon> </customUI>

You can assign your own macro to a built-in control. This is known as repurposing the control. The RibbonX code that follows intercepts three built-in commands:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <commands> <command idMso="FileSave" onAction="mySave"/> <command idMso="FilePrint" onAction="myPrint"/> <command idMso="FilePrintQuick" onAction="myPrint"/> </commands> </customUI>

You can also write RibbonX code to disable one or more built-in controls. The code that follows disables the Insert ClipArt command.

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui"> <commands> <command idMso="ClipArtInsert" enabled="false"/> </commands> </customUI>

If you have two or more workbooks (or add-ins) that add controls to the same custom Ribbon group, you must make sure that they both use the same namespace. Do this in the <CustomUI> tag at the top of the RibbonX code.

A simple RibbonX example

More about the simple RibbonX example

VBA CALLBACK PROCEDURES

THE .RELS FILE

THE RIBBONX CODE

Another RibbonX example

THE RIBBONX CODE

THE VBA CODE

Ribbon controls demo

CREATING A NEW TAB

CREATING A RIBBON GROUP

CREATING CONTROLS

A DynamicMenu Control Example

More on Ribbon customization

Категории