Calling Excel 2007 Custom Wizards from the Office Fluent Ribbon by Using VBA
Summary: Learn how to create custom wizards in Microsoft Office Excel 2007 to collect and display information by using VBA code. Find out how to modify the 2007 Microsoft Office Fluent Ribbon to display the wizard. (10 printed pages)
Frank Rice, Microsoft Corporation
Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007
This article demonstrates two ways to add functionality to Microsoft Office Excel 2007. First, you create a simple multi-page wizard. Second, you add controls to the 2007 Microsoft Office Fluent Ribbon to display the wizard. You also add Microsoft Visual Basic for Applications (VBA) code that gives the wizard its functionality and ties the wizard to the Office Fluent Ribbon user interface (UI).
In the procedures in this section, you create the custom wizard. The wizard is a user form consisting of a series of pages where you select various options and enter text. The wizard tracks this information and displays it on a summary page. Figure 1 shows the first screen of the wizard.
Figure 1. First screen of the custom wizard
The four screens of the wizard are actually pages in a MultiPage control. You can change pages by using navigation buttons or by clicking tabs. Two other buttons allow you to cancel and exit the wizard. Your choices are presented as option button controls. The wizard also has a text box where you type information and another that displays the summary information collected by the wizard.
To create the macro-enabled workbook
Start Microsoft Office Excel 2007.
To create a macro-enabled workbook, click the Microsoft Office Button, point to Save As, and click Excel Macro-Enabled Workbook.
In the Save As dialog box, name the workbook LaunchWizardSample.xlsm and then click Save.
To display the Visual Basic Editor, click the Developer tab and then click Visual Basic.
If you do not see the Developer tab, to add it, you can click the Microsoft Office button, click Excel Options, click the Popular tab, and then select the Show Developer tab in the Ribbon check box.
To create the user form
In the Visual Basic Editor, click Insert, and then click UserForm.
Select the form, and in the Properties pane, change the Name property to SampleWizard.
To display the designer surface of the user form, click the Forms folder in the Project pane, right-click SampleWizard, and select View Object.
On the View menu, click Toolbox.
In the toolbox, drag a MultiPage control onto the user form and resize the control so that the form looks like Figure 1.
In the Properties pane, change the Caption property of the page to Step 1.
Make sure that the Enabled property is set to True.
From the toolbox, drag a Frame control onto the page.
Change the Caption property of the frame to Select a product.
From the toolbox, drag four option buttons onto the frame and set the following properties.
On the user form tab labeled Step 1, right-click, and then click Add Page.
Repeat Step 11 to add two additional pages to the form and set the properties of all three pages as described in Table 2.
On the form tab labeled Step 2, drag a Frame control onto the page and change the Caption property to Choose a shipping option.
From the toolbox, drag three option buttons onto the frame and set the following properties.
Standard (7 - 10 days)
Express (3 - 5 days)
On the form tab labeled Step 3, drag a Frame control onto the page and change the Caption property to Type message for the accompanying card (3 lines max).
From the toolbox, drag a text box onto the frame and change the Name property to txtMessage. The resulting page looks like Figure 3.
On the form tab labeled Summary, drag a Frame control onto the page and change the Caption property to Your selections.
Drag a text box onto the frame and change the Name property to txtSummary.
Change the BackColor property to &H8000000F, which is the same color as the form background. This indicates to the user that this information cannot be changed. The resulting page looks like Figure 4.
Next, you add code to the user form that gives it functionality. In the following procedure, you add code to react when users move from page to page in the wizard.
To add functionality to the user form
In the area just below the tabs in the MultiPage control, double-click to open the code screen of the user form. This creates the Change event handler for the MultiPage control.
Replace the procedure with the following code.
Private Sub MultiPage1_Change() If MultiPage1.Value = 0 Then btnPrev.Enabled = False btnNext.Enabled = True SampleWizard.Caption = "Sample Wizard - Step 1 of 3" ElseIf MultiPage1.Value = 1 Then btnPrev.Enabled = True btnNext.Enabled = True SampleWizard.Caption = "Sample Wizard - Step 2 of 3" ElseIf MultiPage1.Value = 2 Then btnPrev.Enabled = True btnNext.Enabled = True SampleWizard.Caption = "Sample Wizard - Step 3 of 3" ElseIf MultiPage1.Value = 3 Then btnPrev.Enabled = True btnNext.Enabled = False SampleWizard.Caption = "Sample Wizard - Summary" SummarizeOptions Else MsgBox "Error: Invalid page value" End If End Sub
This procedure is triggered anytime that the MultiPage control is changed such as when the user moves from one page to another. This code sets the behavior of the wizard. As the user moves from one page to another, the Enabled property of the navigation buttons are set and the caption of the user form is changed to reflect which page is displayed. Each time there is a change, the SummarizeOptions procedure is called.
Below the MultiPage1_Change method add the following code.
Private Sub SummarizeOptions() If optFlowers.Value Then txtSummary.Text = txtSummary.Text & "You selected flowers." ElseIf optCandy.Value Then txtSummary.Text = txtSummary.Text & "You selected candy." ElseIf optFruit.Value Then txtSummary.Text = txtSummary.Text & "You selected fruit" ElseIf optTickets.Value Then txtSummary.Text = txtSummary.Text & "You selected flowers." Else txtSummary.Text = txtSummary.Text & "No product was selected." End If If optStandard.Value Then txtSummary.Text = txtSummary.Text & vbCrLf & "You selected standard shipping." ElseIf optExpress.Value Then txtSummary.Text = txtSummary.Text & vbCrLf & "You selected express shipping." ElseIf optOvernight.Value Then txtSummary.Text = txtSummary.Text & vbCrLf & "You selected overnight shipping." Else txtSummary.Text = txtSummary.Text & vbCrLf & "No shipping method was selected." End If txtSummary.Text = txtSummary.Text & vbCrLf & "Your card will say:" & vbCrLf txtSummary.Text = txtSummary.Text & txtMessage.Text End Sub
This procedure collects the user's selections as she moves from page to page. The collected information is displayed on the summary page. Each option is tested for a value, and if it evaluates to True, then its value is assigned to the summary text box on the summary page of the wizard. In addition, the text the user types into the text box on the third page of the wizard is collected and displayed on the summary page.
Next, add navigation buttons to the form.
To add navigation buttons to the form
From the toolbox, drag four buttons onto the bottom of the form in the order shown in Figure 1 and set their properties as described in Table 4.
To create the Click event handler for the navigation buttons, double-click the Cancel button.
Insert the End command into the procedure code. This command closes the form when a user clicks Cancel.
Double-click the < Prev button and replace the event handler procedure with the following code.
Private Sub btnPrev_Click() Dim i As Long i = MultiPage1.Value - 1 If i >= 0 Then MultiPage1.Value = i End If End Sub
This procedure determines the new page to display by subtracting 1 from the current page by using the Value property of the MultiPage control. If the new page number is equal to or greater than zero, then the new page number is assigned to the value. Otherwise, the button click is ignored.
Double-click the Next > button and replace the event handler procedure with the following code.
Private Sub btnNext_Click() Dim i As Long i = MultiPage1.Value + 1 If i < MultiPage1.Pages.Count Then MultiPage1.Value = i End If End Sub
This procedure resembles the previous one, but instead adds 1 to the Value property and ensures that the new page value is less than the total count of pages.
To navigate from page to page, you can also click the page's tab.
Double-click the Finish button and replace the existing procedure with the following code.
In this solution, you use document-level customization of the Office Fluent Ribbon. In a typical scenario, you create a customization file that contains the XML that defines the structure of the Office Fluent Ribbon. You name this file customUI.xml and save it in a folder named CustomUI. You then manually add this folder to a macro-enabled Excel 2007 (.xlsm) file opened as a Zip package. Next, you modify a relationship part in the package to tie the customUI XML part to the main document.
For detailed information about adding controls to the Office Fluent Ribbon, see Customizing the 2007 Office Fluent Ribbon for Developers (Part 1 of 3).
You can automate much of this process with a free tool named the Microsoft Office 2007 Custom UI Editor. The tool is a Windows form that puts your customUI part into the package and defines a relationship to it, all with a single mouse click. The tool works with either an Excel add-in (.xlam) or a macro-enabled workbook (.xlsm). When you install the tool, the default location is C:\Program Files\CustomUIEditor. An icon is also added to your Start menu.
In the following steps, you add a custom tab, group, and button to the Office Fluent Ribbon by using the Custom UI Editor tool. When you click the button, the wizard is displayed.
To add controls to the Office Fluent Ribbon
After installing the Custom UI Editor tool, click Start, point to All Programs, and then click Office 2007 Custom UI Editor. The Custom UI Editor dialog is displayed as shown in Figure 5.
On the File menu, click Open, navigate to the LaunchWizardSample.xlsm workbook, and then click Open.
In the Custom UI pane, insert the following XML markup.
<?xml version="1.0" encoding="utf-8" ?> <customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui" > <ribbon> <tabs> <tab id="myTab" label="My Sample Tab"> <group id="group1" label="My Group"> <button id="myButton" label="Run Wizard" imageMso="HappyFace" size="large" onAction="myButton_ClickHandler" /> </group> </tab> </tabs> </ribbon> </customUI>
This XML markup adds a custom tab to the Office Fluent Ribbon. Inside the tab, a group is added and inside the group, a button. The button has a label and a built-in icon. In addition, the onAction attribute points to an event procedure that is triggered when you click the button.
Images built into 2007 Microsoft Office have the Mso identifier appended to the image attribute name. Custom images have the image attribute name without the Mso appended and point to a custom image file.
On the File menu, click Save, and close the editor.
In the Excel Visual Basic Editor, add a module to hold the button's event procedure.
On the Insert menu, click Module.
In the Properties pane, change the name of the module to mRibbon.
In the Project pane, double-click the module that you just added to display its code window.
In the code window, add the following procedure.
When you click the Run Wizard button, this procedure displays the user form by calling the Show method.
Save and close the project. Close the workbook.
In the following steps you test the project by displaying the wizard from the Office Fluent Ribbon. Additionally, you set options and enter text into the wizard and then display a summary of the results.
To test the project
Open the LaunchWizardSample.xlsm workbook. Notice the My Sample Tab tab on the right side of the Office Fluent Ribbon, as seen in Figure 6.
Click the My Sample Tab tab and then click Run Wizard. The Sample Wizard is displayed, as seen in Figure 7.
On the Step 1 tab, click Flowers and then click Next >.
On the Step 2 tab, click Express (3 - 5 days) and then click Step 3.
In the text box, type Hello World and then click Next. All of your selections are summarized on the page, as seen in Figure 8.
Click Finish to close the wizard and then close the workbook.
Wizards provide an easy way to collect information from a user. As you have seen in this article, building a custom wizard is not difficult. The 2007 Microsoft Office Fluent Ribbon is easily customizable and lends itself as an ideal platform for launching dialog boxes or wizards. Customizing the Office Fluent Ribbon and adding custom wizards give your applications a professional look and feel.
You can find more information on the procedures discussed in this article in the following resources: