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

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.

Figure 15-9: This four-step wizard uses a 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.

Setting up the MultiPage control for the 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.

Adding the buttons to the wizard UserForm

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 .

Programming the wizard buttons

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

Figure 15-10: Clicking the Cancel button displays a confirmation message box.

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.

Programming dependencies in a wizard

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

Performing the task with the wizard

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.