Many applications incorporate wizards to guide users through an operation. Excel's Text Import Wizard is a good example. A wizard is essentially a series of dialog boxes that solicit information from the user. Usually, the user's choices in earlier dialog boxes influence the contents of later dialog boxes. In most wizards, the user is free to go forward or backward through the dialog box sequence or to click the Finish button to accept all defaults.
You can create wizards by using VBA and a series of UserForms. However, I've found that the most efficient way to create a wizard is to use a single UserForm and a MultiPage control with the tabs hidden.
Figure 15-9 shows an example of a simple four-step wizard, which consists of a single UserForm that contains a MultiPage control. Each step of the wizard displays a different page in the MultiPage control.
CD-ROM | The wizard example in this section is available on the companion CD-ROM. The file is named wizard demo.xlsm . |
The sections that follow describe how I created the sample wizard.
Start with a new UserForm and add a MultiPage control. By default, this control contains two pages. Right-click the MultiPage tab and insert enough new pages to handle your wizard (one page for each wizard step). The example on the CD-ROM is a four-step wizard, so the MultiPage control has four pages. The names of the MultiPage tabs are irrelevant because they will not be seen. The MultiPage control's Style property will eventually be set to 2 - fmTabStyleNone .
Tip | While working on the UserForm, you'll want to keep the MultiPage tabs visible to make it easier to access various pages. |
Next , add the desired controls to each page of the MultiPage control. This will, of course, vary depending on your application. You might need to resize the MultiPage control while you work in order to have room for the controls.
Now add the buttons that control the progress of the wizard. These buttons are placed outside the MultiPage control because they are used while any of the pages are displayed. Most wizards have four buttons:
Cancel: Cancels the wizard and performs no action.
Back: Returns to the previous step. During Step 1 of the wizard, this button should be disabled.
Next: Advances to the next step. During the last wizard step, this button should be disabled.
Finish: Finishes the wizard.
Note | In some cases, the user is allowed to click the Finish button at any time and accept the defaults for items that were skipped over. In other cases, the wizard requires a user response for some items. If this is the case, the Finish button is disabled until all required input is made. The example on the CD-ROM requires an entry in the TextBox in Step 1. |
In the example, these CommandButtons are named CancelButton , BackButton , NextButton , and FinishButton .
Each of the four wizard buttons requires a procedure to handle its Click event. The event handler for CancelButton follows . This procedure uses a MsgBox function (see Figure 15-10) to verify that the user really wants to exit. If the user clicks the Yes button, the UserForm is unloaded with no action taken. This type of verification, of course, is optional.
Private Sub CancelButton_Click() Dim Msg As String Dim Ans As Integer Msg = "Cancel the wizard?" Ans = MsgBox(Msg, vbQuestion + vbYesNo, APPNAME) If Ans = vbYes Then Unload Me End Sub
The event handler procedures for the Back and Next buttons follow:
Private Sub BackButton_Click() MultiPage1.Value = MultiPage1.Value - 1 UpdateControls End Sub Private Sub NextButton_Click() MultiPage1.Value = MultiPage1.Value + 1 UpdateControls End Sub
These two procedures are very simple. They change the Value property of the MultiPage control and then call another procedure named UpdateControls (which follows).
The UpdateControls procedure is responsible for enabling and disabling the BackButton and NextButton controls.
Sub UpdateControls() Select Case MultiPage1.Value Case 0 BackButton.Enabled = False NextButton.Enabled = True Case MultiPage1.Pages.Count - 1 BackButton.Enabled = True NextButton.Enabled = False Case Else BackButton.Enabled = True NextButton.Enabled = True End Select ' Update the caption Me.Caption = APPNAME & " Step " _ & MultiPage1.Value + 1 & " of " _ & MultiPage1.Pages.Count ' The Name field is required If tbName.Text = "" Then FinishButton.Enabled = False Else FinishButton.Enabled = True End If End Sub
The procedure changes the UserForm's caption to display the current step and the total number of steps. APPNAME is a public constant, defined in Module1 . The procedure then examines the name field on the first page (a TextBox named tbName ). This is a required field, so the user can't click the Finish button if it's empty. If the TextBox is empty, the FinishButton is disabled; otherwise , it's enabled.
In most wizards, a user's response on a particular step can affect what's displayed in a subsequent step. In this example, the user indicates which products he or she uses in Step 3 and then rates those products in Step 4. The OptionButtons for a product's rating are visible only if the user has indicated a particular product.
Programmatically, this is accomplished by monitoring the MultiPage's Change event. Whenever the value of the MultiPage is changed (by clicking the Back or Next button), the MultiPage1_Change procedure is executed. If the MultiPage control is on the last tab (Step 4), the procedure examines the values of the CheckBox controls in Step 3 and makes the appropriate adjustments in Step 4.
In this example, the code uses two arrays of controls - one for the product CheckBox controls (Step 3) and one for the Frame controls (Step 4). The code uses a For-Next loop to hide the Frames for the products that are not used and then adjusts their vertical positioning. If none of the check boxes in Step 3 is checked, everything in Step 4 is hidden except a TextBox that displays Click Finish to exit (if a name is entered in Step 1) or A name is required in Step 1 (if a name is not entered in Step 1). The MultiPage1_Change procedure follows:
Private Sub MultiPage1_Change() ' Set up the Ratings page? If MultiPage1.Value = 3 Then ' Create an array of CheckBox controls Dim ProdCB(1 To 3) As MSForms.CheckBox Set ProdCB(1) = cbExcel Set ProdCB(2) = cbWord Set ProdCB(3) = cbAccess ' Create an array of Frame controls Dim ProdFrame(1 To 3) As MSForms.Frame Set ProdFrame(1) = FrameExcel Set ProdFrame(2) = FrameWord Set ProdFrame(3) = FrameAccess TopPos = 22 FSpace = 8 AtLeastOne = False ' Loop through all products For i = 1 To 3 If ProdCB(i) Then ProdFrame(i).Visible = True ProdFrame(i).Top = TopPos TopPos = TopPos + ProdFrame(i).Height + FSpace AtLeastOne = True Else ProdFrame(i).Visible = False End If Next i ' Uses no products? If AtLeastOne Then lblHeadings.Visible = True Image4.Visible = True lblFinishMsg.Visible = False Else lblHeadings.Visible = False Image4.Visible = False lblFinishMsg.Visible = True If tbName = "" Then lblFinishMsg.Caption = _ "A name is required in Step 1." Else lblFinishMsg.Caption = _ "Click Finish to exit." End If End If End If End Sub
When the user clicks the Finish button, the wizard performs its task: transferring the information from the UserForm to the next empty row in the worksheet. This procedure, named FinishButton_Click , is very straightforward. It starts by determining the next empty worksheet row and assigns this value to a variable ( r ). The remainder of the procedure simply translates the values of the controls and enters data into the worksheet.
Private Sub FinishButton_Click() r = Application.WorksheetFunction. _ CountA(Range("A:A")) + 1 ' Insert the name Cells(r, 1) = tbName.Text ' Insert the gender Select Case True Case obMale: Cells(r, 2) = "Male" Case obFemale: Cells(r, 2) = "Female" Case obNoAnswer: Cells(r, 2) = "Unknown" End Select ' Insert usage Cells(r, 3) = cbExcel Cells(r, 4) = cbWord Cells(r, 5) = cbAccess ' Insert ratings If obExcel1 Then Cells(r, 6) = "" If obExcel2 Then Cells(r, 6) = 0 If obExcel3 Then Cells(r, 6) = 1 If obExcel4 Then Cells(r, 6) = 2 If obWord1 Then Cells(r, 7) = "" If obWord2 Then Cells(r, 7) = 0 If obWord3 Then Cells(r, 7) = 1 If obWord4 Then Cells(r, 7) = 2 If obAccess1 Then Cells(r, 8) = "" If obAccess2 Then Cells(r, 8) = 0 If obAccess3 Then Cells(r, 8) = 1 If obAccess4 Then Cells(r, 8) = 2 ' Unload the form Unload Me End Sub
After you test your wizard, and everything is working properly, you can set the MultiPage control's Style property to 2 - fmTabStyleNone to hide the tabs.