Share via

Calling Excel 2007 Custom Wizards from the Office Fluent Ribbon by Using VBA

  • Article

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

November 2007

Applies to: 2007 Microsoft Office System, Microsoft Office Excel 2007

Contents

  • Overview

  • Creating the Custom Wizard

  • Adding Controls to the Office Fluent Ribbon

  • Testing the Project

  • Conclusion

  • Additional Resources

Overview

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

Creating the Custom Wizard

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

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

  1. Start Microsoft Office Excel 2007.

  2. To create a macro-enabled workbook, click the Microsoft Office Button, point to Save As, and click Excel Macro-Enabled Workbook.

  3. In the Save As dialog box, name the workbook LaunchWizardSample.xlsm and then click Save.

  4. To display the Visual Basic Editor, click the Developer tab and then click Visual Basic.

    NoteNote

    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

  1. In the Visual Basic Editor, click Insert, and then click UserForm.

  2. Select the form, and in the Properties pane, change the Name property to SampleWizard.

  3. To display the designer surface of the user form, click the Forms folder in the Project pane, right-click SampleWizard, and select View Object.

  4. On the View menu, click Toolbox.

  5. In the toolbox, drag a MultiPage control onto the user form and resize the control so that the form looks like Figure 1.

  6. In the Properties pane, change the Caption property of the page to Step 1.

  7. Make sure that the Enabled property is set to True.

  8. From the toolbox, drag a Frame control onto the page.

  9. Change the Caption property of the frame to Select a product.

  10. From the toolbox, drag four option buttons onto the frame and set the following properties.

    Table 1. Set product button properties

    Name

    Caption

    optFlowers

    Flowers

    optCandy

    Candy

    optFruit

    Fruit

    optTickets

    Theatre Tickets

  11. On the user form tab labeled Step 1, right-click, and then click Add Page.

  12. Repeat Step 11 to add two additional pages to the form and set the properties of all three pages as described in Table 2.

    Table 2. Set the page properties

    Name

    Caption

    Enabled

    Page2

    Step 2

    True

    Page3

    Step 3

    True

    Page4

    Summary

    True

  13. On the form tab labeled Step 2, drag a Frame control onto the page and change the Caption property to Choose a shipping option.

  14. From the toolbox, drag three option buttons onto the frame and set the following properties.

    Table 3. Set shipping button properties

    Name

    Caption

    optStandard

    Standard (7 - 10 days)

    optExpress

    Express (3 - 5 days)

    optOvernight

    Overnight

    Figure 2. Page two of the custom wizard

    Page two of wizard

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

  16. From the toolbox, drag a text box onto the frame and change the Name property to txtMessage. The resulting page looks like Figure 3.

    Figure 3. Page three of the custom wizard

    Page three of wizard

  17. On the form tab labeled Summary, drag a Frame control onto the page and change the Caption property to Your selections.

  18. Drag a text box onto the frame and change the Name property to txtSummary.

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

    Figure 4. Summary page of the custom wizard

    Summary page of wizard

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

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

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

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

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

    Table 4. Set navigation button properties

    Name

    Caption

    btnCancel

    Cancel

    btnPrev

    < Prev

    btnNext

    Next >

    btnFinish

    Finish

  2. To create the Click event handler for the navigation buttons, double-click the Cancel button.

  3. Insert the End command into the procedure code. This command closes the form when a user clicks Cancel.

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

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

    NoteNote

    To navigate from page to page, you can also click the page's tab.

  6. Double-click the Finish button and replace the existing procedure with the following code.

    Private Sub btnFinish_Click()
    MsgBox "Thank you for shopping with us."
    End
End Sub

Adding Controls to the Office Fluent Ribbon

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.

NoteNote

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

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

    Figure 5. Microsoft Office 2007 Custom UI Editor tool

    Microsoft Office 2007 Custom UI Editor tool

  2. On the File menu, click Open, navigate to the LaunchWizardSample.xlsm workbook, and then click Open.

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

    NoteNote

    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.

  4. On the File menu, click Save, and close the editor.

  5. In the Excel Visual Basic Editor, add a module to hold the button's event procedure.

  6. On the Insert menu, click Module.

  7. In the Properties pane, change the name of the module to mRibbon.

  8. In the Project pane, double-click the module that you just added to display its code window.

  9. In the code window, add the following procedure.

    Sub myButton_ClickHandler(control As IRibbonControl)
    SampleWizard.Show
End Sub

    When you click the Run Wizard button, this procedure displays the user form by calling the Show method.

  10. Save and close the project. Close the workbook.

Testing the Project

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

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

    Figure 6. My Sample Tab with the group and button

    Sample tab with the group and button

  2. Click the My Sample Tab tab and then click Run Wizard. The Sample Wizard is displayed, as seen in Figure 7.

    Figure 7. First page of the custom wizard

    Opening page of wizard

  3. On the Step 1 tab, click Flowers and then click Next >.

  4. On the Step 2 tab, click Express (3 - 5 days) and then click Step 3.

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

    Figure 8. Summary page of the custom wizard

    Summary page of wizard

  6. Click Finish to close the wizard and then close the workbook.

Conclusion

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.

Additional Resources

You can find more information on the procedures discussed in this article in the following resources: