Building a Multi-Step Wizard

---

The other example in this chapter shows you how to build a multi-step wizard that's invoked from Excel's menus. This example gives you the framework with which to develop your own wizards.

Wizard Application Overview

The wizard in this example merely collects information over several steps and summarizes the information in the final step. Step 1 of the wizard (shown in Figure 20-5) presents the user a choice of four different options using OptionButton controls that are nested inside a single frame control. Notice that the Prev button is disabled because this is the first step of the wizard.

Figure 20-5: Step 1 of the wizard allows the user to select from multiple options using the OptionButton controls.

Step 2 of the wizard allows the user to enter values into two distinct text box controls. (See Figure 20-6.) Again, a frame control is used to provide instructions to the user.

Figure 20-6: The user can enter information into multiple text boxes in step 2 of the wizard.

In the final step of the wizard, the user is given a chance to review the information entered in the previous steps of the wizard. (See Figure 20-7.) The Next button is disabled because there are no subsequent steps. To end the wizard, the user can press either the Cancel or the Finish button.

Figure 20-7: Step 3 of the wizard lets the user review all of her choices before clicking Finish.

Note 

CD-ROM The complete source code for this example is found on the CD in WizardApp.xls.

Handling Menus

The first step in building the wizard application is to trap the workbook's Open event to add the necessary menu button. This also means that the BeforeClose event should also remove the menu button.

In the Workbook_Open event in the ThisWorkbook module (see the following listing), a command bar button is added to the Tools menu. The first step is to locate the Worksheet Menu Bar through the Application.CommandBars collection. Once the proper command bar is located, then the specific popup control for the Tools menu is located. Finally, a new command button is added to the end of the popup control.

Private Sub Workbook_Open()
 
Dim c As CommandBar
Dim cb As CommandBarButton
Dim cp As CommandBarPopup
 
On Error Resume Next
Set c = Application.CommandBars("Worksheet Menu Bar")
If Not c Is Nothing Then
    Set cp = c.Controls("&Tools")
    
    If Not cp Is Nothing Then
        Set cb = cp.Controls.Add(msoControlButton)
        cb.Tag = "Excel 2k3 WizardApp"
        cb.Style = msoButtonCaption
        cb.Caption = "Excel 2k3 Wizard"
        cb.OnAction = "ThisWorkbook.RunWizard"
      
    End If
 
End If
 
End Sub 

The new command button will fire the ThisWorkbook.RunWizard subroutine when the new button is clicked. The only thing that the RunWizard routine does is show the wizard's user form using one line of code.

Public Sub RunWizard
 
UserForm1.Show vbModal
 
End Sub

Notice that the Tag property in the Workbook_Open routine is set to a unique value, to make it easy to remove the button in the Workbook_BeforeClose event. (See the following listing.)

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 
Dim c As CommandBar
Dim cb As CommandBarButton
 
On Error Resume Next
Set c = Application.CommandBars("Worksheet Menu Bar")
If Not c Is Nothing Then
    Set cb = c.FindControl(, , "Excel 2k3 WizardApp", , True)
    Do While Not cb Is Nothing
        cb.Delete
        Set cb = c.FindControl(, , "Excel 2k3 WizardApp", , True)
 
    Loop
    
End If
 
End Sub

The code for the Workbook_BeforeClose event is probably more complex than is really needed, but it also ensures that any buttons associated with the wizard application are deleted. The code merely locates the first control that contains Excel 2k3 Wizard App in the tag property using the FindControl method. Then the code enters a While loop that will delete this specific control and then search for the next control with the same Tag value.

Building the UserForm

Because the wizard displays several forms' worth of information, it's natural to use the MultiPage control. The MultiPage control has several properties that make it very useful for this particular situation. First, the MultiPage control contains a number of individual Page objects.

For more information about the MultiPage control and the Page objects, see Chapter 19, 'Creating User Forms.'

Each Page object is a control container, which means that you can drag multiple controls onto each page and access them as if they were placed directly on the form. The real strength of the MultiPage control is its ability to switch from one page to another by merely updating the Value property. This means that you can prepare a Page object for each step of the wizard and then display the Page object that corresponds to the appropriate step of the wizard.

To maneuver through the steps of the wizard, you need to add four command button controls at the bottom of the form. Since these buttons are outside of the MultiPage control, they will always appear on each step of the wizard. Set the captions for these buttons to Cancel, < Prev, Next >, and Finish.

Because this wizard has three steps, you need to add a third page. Right-click over the tab area, and select New Page from the popup menu. This will add a new Page object to the MultiPage control. (See Figure 20-8.) Then change the caption property for each page to Step 1, Step 2, and Step 3.

Figure 20-8: Right-click over the tab area, and select New Page from the popup menu to add a new Page to the MultiPage control.

Navigating the Pages

The four buttons at the bottom of the page are the primary tool for navigation in the wizard. Pressing the Cancel button triggers the CommandButton1_Click event, which runs the End statement to stop the program. Pressing the Prev or Next buttons moves the wizard to the previous or next step, respectively. The Finish button is the only way to trigger the final execution of the wizard.

Private Sub CommandButton1_Click()
 
End
 
End Sub

When the user presses the Prev button, the event associated with the control in the user form module in the following listing is executed. This code computes the new page to be displayed by subtracting 1 from the current page using the Value property. If the new page number is greater than or equal to zero, then the new page number is assigned to the Value property; otherwise, the click is just ignored.

Private Sub CommandButton2_Click()
 
Dim i As Long
 
i = MultiPage1.Value - 1
 
If i >= 0 Then
    MultiPage1.Value = i
   
End If
 
End Sub

The Next button uses similar code, but increments the MultiPage control's Value property and verifies that it is less than MultiPage1.Pages.Count.

Private Sub CommandButton3_Click()
 
Dim i As Long
 
i = MultiPage1.Value + 1
 
If i < MultiPage1.Pages.Count Then
    MultiPage1.Value = i
      
End If
 
End Sub

In addition to using the Prev and Next buttons, the user can directly select one of the wizard's steps from the tabs at the top of the MultiPage control. You can easily hide the tabs by setting the multi-page control's Style property to fmTabStyleNone.

Tip 

Finding Hidden Controls
If a control is hidden on the form and you want to change one of its properties, simply select the desired control from the drop-down list of controls at the top of the Properties window.

Any time the Value property of the MultiPage control is changed, the control's Change event is fired. The MultiPage1_Change event found in the user form module is the real heart of the wizard's control. Each possible page value is tested and the code appropriate for that page is executed.

Private Sub MultiPage1_Change()
 
If MultiPage1.Value = 0 Then
    CommandButton2.Enabled = False
    CommandButton3.Enabled = True
    UserForm1.Caption = "Wizard App - Step 1 of 3"
    
ElseIf MultiPage1.Value = 1 Then
    CommandButton2.Enabled = True
    CommandButton3.Enabled = True
    UserForm1.Caption = "Wizard App - Step 2 of 3"
    
ElseIf MultiPage1.Value = 2 Then
    CommandButton2.Enabled = True
    CommandButton3.Enabled = False
    UserForm1.Caption = "Wizard App - Step 3 of 3"
    GenerateOptions
    
Else
    MsgBox "Error: invalid page value"
    
End If
 
End Sub

For the first page (Value = 0), the Prev button is disabled, the Next button is enabled, and the user form's Caption property is updated to reflect that this is the first step of the wizard. The Prev button is disabled because it's impossible to move before the first step in the wizard. If the user wants to end the wizard, the Cancel button can be pressed.

On the second page (Value = 1), both the Prev and Next buttons are enabled because the user can choose to press either button. The user form's Caption property is also updated.

On the last page (Value = 2), the Next button is disabled because there are no other steps in the wizard. Unlike the other steps in this wizard, there's an extra line of code that prepares the information on the form before the form is displayed to the user. A call to GenerateOptions handles the necessary work.

If the Value property doesn't match any of the pages associated with the steps of the wizard, a message box displays an error message. In theory, you should never see this message. In practice, it can be very useful when debugging the navigation logic.

Collecting Options for the Wizard

Now that the framework for running the wizard is in place, it's time to show how you might collect some information from the user. These steps demonstrate some of the ways you might collect information. You'll need to determine what information your wizard really needs to collect to perform the task assigned to the wizard.

In step 1 of the wizard, a Frame control surrounds a set of four OptionButton controls. (See Figure 20-9.) This allows the user to choose any one of four different options without any programming.

Figure 20-9: A Frame control surrounds a set of OptionButtons.

In step 2 of the wizard, another Frame control is used to provide a consistent look and feel with step 1. Within the frame, two label and text box controls are displayed to capture other information. (See Figure 20-10.) The text box controls are named Header and Footer to match the Captions displayed on the label controls beside them.

Figure 20-10: A Frame control surrounds a collection of label and text box controls.

In the final step of the wizard, shown in Figure 20-11, yet another Frame control provides a container for a text box control called Review. This text box has the MultiLine property set to True and the BackColor property set to &H8000000F, which is the same color as the background of the form. This indicates to the user that the data contained in the text box can't be changed.

Figure 20-11: A summary of the options selected is displayed in the Review text box.

To prevent the user from changing the information in the Review text box, the Review_KeyPress event is used to suppress any character typed. Setting the KeyAscii argument to zero means that the character the user pressed will not be added to the text box.

Private Sub Review_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 
KeyAscii = 0
 
End Sub

Summarizing the Options

The GenerateOptions routine (shown in the following listing) combines the information collected in the previous steps of the wizard and displays it for the user's review. This routine is located in the user form module. Remember that this routine accesses the controls that were placed on the other pages of the MultiPage control as if they were directly on the user form itself.

Private Sub GenerateOptions()
 
Review.Text = "Header: " & Header.Text & vbCrLf
 
If OptionButton1.Value Then
    Review.Text = Review.Text & "Option 1 was selected"
    
ElseIf OptionButton2.Value Then
Review.Text = Review.Text & "Option 2 was selected"
 
ElseIf OptionButton3.Value Then
    Review.Text = Review.Text & "Option 3 was selected"
 
ElseIf OptionButton4.Value Then
    Review.Text = Review.Text & "Option 4 was selected"
 
Else
    Review.Text = Review.Text & "No options were selected"
    
End If
 
Review.Text = Review.Text & vbCrLf
 
Review.Text = Review.Text & "Footer: " & Footer.Text
 
End Sub

This routine uses a multi-line text box control on which the various choices made by the user are displayed. In this example, the information from the various controls is copied to the multi-line text box. Notice that a vbCrLf is appended to the Text property after each line of information is generated. This forces the next line to be displayed starting at the left edge of the control.

Running the Wizard

Pressing the Finish button actually runs the wizard. In this example, the wizard's execution consists of displaying a message box and then ending the program. In a real wizard, the MsgBox statement would be replaced with a call to a subroutine that takes the information collected through the various steps of the wizard and performs whatever task the wizard was designed to perform.

Private Sub CommandButton4_Click()
 
MsgBox "Ending the wizard"
 
End
 
End Sub

More typically, this routine would collect information collected from the various controls on the user form and then execute whatever function that the wizard was supposed to perform.

It's important to remember that the user may choose to press the Finish button at any time while the wizard is active. Therefore, it might be desirable to give each control on the wizard a meaningful default value so that pressing the Finish button will produce a useful result.

If you choose not to give each control a meaningful value, you should examine the information stored in the controls to determine if there is sufficient information to produce a useful result. If a user hasn't given the wizard enough information to produce a useful result, your verification routine should display the wizard step where the user can supply the information.

In this chapter, you saw how to build two different applications that rely on user forms. In the first application, you saw how you can easily build a user form that allows someone to edit the data contained in a worksheet. By providing a user form, you can insure that the data that's entered into the worksheet is both valid and properly formatted. In the second application, you saw how to build a multi-step wizard with a single a user form and MultiPage control. Each Page in the MultiPage control allows you to create a custom appearance for each step of the wizard, and using a single user form simplifies the development process.

---