Microsoft Office Excel 2003 Programming Inside Out

 

This article is an excerpt from Microsoft Office Excel 2003 Programming Inside Out, from Microsoft Press (ISBN 0-7356-1985-9, copyright Microsoft Press 2004, all rights reserved). The authors of the book are Curtis Frye, Wayne S. Freeze, and Felicia K. Buckingham.

Curtis Frye is the author of Microsoft Office Excel 2003 Step by Step*,* Microsoft Excel Version 2002 Plain & Simple*,* Faster Smarter Home Networking*, several books on Microsoft Access, and numerous online training courses.*

Wayne S. Freeze, a computer consultant and author with more than a dozen books and more than 50 articles to his credit, specializes in programming for Microsoft Office, Microsoft SQL Server, and Microsoft DirectX.

Felicia K. Buckingham, an expert on Excel and Microsoft Visual Basic for Applications (VBA) programming, is the principal of FKB Consulting, based in Manitoba, Canada.

No part of this chapter may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

Chapter 20: Creating Advanced User Forms

Contents

Capturing Information
Building a Multi-Step Wizard

User forms are a critical part of Microsoft Excel programming in that they provide a surface that is totally under your control with which you can interact with a user. This makes it possible to build more complex Excel applications. Forms can also be used to collect and verify information from a user before it's entered into a worksheet. They can also be used as part of an add-in to display options and control execution of a particular task. In this chapter, you'll learn how to build a user form that allows a user to input data into the worksheet, plus how to build an add-in that invokes a multi-step wizard that creates a chart based on selections made by a user.

Capturing Information

Entering information into a worksheet can be painful sometimes. It's difficult to ensure that the data is valid and is properly formatted. It can also be difficult to ensure that the data is added at the proper location. In this example, you'll learn how to construct a simple form that accepts data from the user and stores it in a worksheet.

Form Application Overview

The Excel worksheet used in this sample application merely records six pieces of information about a customer at The Garden Company: CustomerId, Name, City, State, ZipCode, and DateAdded. (See Figure 20-1.)

This form is started by running a macro and remains up until the user explicitly closes the form. Buttons on the form control which row of the worksheet is displayed through the form, and the form itself allows the user to enter or edit any data stored in a particular row.

Figure 20-1. A simple worksheet tracks customer information such as name and address and the date the customer was added.

Designing a Form

For most data-driven forms, you should place the fields in a single column with labels next to each field. This arrangement makes it easy for the user to find information on the form. However, with some fields, such as those that are part of an address, you might find it better to arrange the fields more intuitively, such as placing the City, State, and ZipCode fields on the same line.

It's important to note that the placement of the fields on the form is completely independent of the code that accesses it. If you wished to arrange all of the fields in a circle, it wouldn't make a bit of difference to your code. While this fact is something that might seem obvious, it was the revolutionary concept that started the Microsoft Visual Basic revolution.

**Tip: Work with What Works   **When designing user forms, take a look at the various windows and dialog boxes built into Excel and other applications for design ideas.

Follow these steps to create a form that will interact with the user:

1. Start the Visual Basic Editor, and choose Insert, UserForm from the main menu. This will create a new UserForm object in your application.

2. From the Toolbox, drag a TextBox control and a Label control for each column in the worksheet. Drag a ComboBox control to hold the list of states. You might have to adjust the size of the user form to accommodate the controls.

   **Tip: Double-Click to Save Time   **If you wish to add multiple copies of the same control to a user form, double-click the control in the toolbox. The mouse pointer will change to reflect the selected control. You may then draw multiple controls on the user form. When you're finished adding that particular control, you may double-click another control in the toolbox to add multiple copies of that control or click the arrow in the toolbox to return the mouse pointer to normal.

3. Use the Properties window to change the Name property of each text box to reflect the database fields (CustomerId, CustomerName, City, ZipCode, and DateAdded). Change the Name property of the combo box control to State. Also change the Caption property for each Label control to something more descriptive. (See Figure 20-2.)

   

   Figure 20-2. Add TextBox and Label controls for each column in the worksheet and their properties as directed.

4. Add CommandButton controls to the user form that will allow the user to scroll through the rows. Change the Caption property to read First, Previous, Next, and Last. Leave a space between the Previous and Next controls.

   **Tip: Controls Have Freedom of Movement   **Once a control is on the form, you can select it and move it anywhere on the form you wish. You can also do the same thing with multiple controls by clicking the form and dragging to select the group of controls you want to move. Then you can drag the selected group around on the form.

5. Add a TextBox control in between the Previous and Next controls. Change the Name property to RowNumber. Set the Text property to 2.

6. Add three more CommandButton controls. Change the Caption property of the first one to Save, the second one to Cancel, and the last one to Add.

7. Change the Enabled property on the Save and CancelCommandButton controls to False.

8. Once all of the controls have been added, you can tweak their sizes and exact placement on the form until you find a pleasing arrangement. (See Figure 20-3.)

   

   Figure 20-3. Finishing the form layout.

Displaying Data

With the form constructed, it's time to copy data from the worksheet to the form. The RowNumber text box contains the number of the row that should be displayed on the form, so the real trick is to convert the value in the RowNumber text box into a value that can be used to extract the data from the worksheet using the Cells method.

The following program listing shows the GetData routine, which is located in the module associated with the user form. GetData copies the data from the currently active worksheet to the user form. After declaring a temporary variable r to hold the current row, the routine verifies that the value in the RowNumber control is numeric. This step is important because the user could type any value into this field.

Private Sub GetData()

Dim r As Long

If IsNumeric(RowNumber.Text) Then
    r = CLng(RowNumber.Text)
    
Else
    ClearData
    MsgBox "Illegal row number"
    Exit Sub
    
End If

If r > 1 And r <= LastRow Then
    CustomerId.Text = FormatNumber(Cells(r, 1), 0)
    CustomerName.Text = Cells(r, 2)
    City.Text = Cells(r, 3)
    State.Text = Cells(r, 4)
    Zip.Text = Cells(r, 5)
    DateAdded.Text = FormatDateTime(Cells(r, 6), vbShortDate)

    DisableSave
    
ElseIf r = 1 Then
    ClearData

Else
    ClearData
    MsgBox "Invalid row number"
    
End If

End Sub

Knowing that RowNumber contains a numeric value, the CLng function is used to convert the value in RowNumber into the variable r. The rest of the code merely uses r to extract the information from the proper row and copy it to the correct field. Otherwise, a message box will be displayed to the user indicating that the row number value is invalid. The ClearData routine simply assigns an empty string to each field on the form to clear out any values that might have already been displayed on the form. (Remember that the ComboBox control can't be set to an empty string and should be set to a valid state value.)

Private Sub ClearData()

CustomerId.Text = ""
CustomerName.Text = ""
City.Text = ""
State.Text = "AK"
Zip.Text = ""
DateAdded.Text = ""

End Sub

Simply because the row number is numeric doesn't mean that it's safe to pass the number to the Cells method. You can add the constant LastRow to the start of the user form module like this while testing this routine. (Later in this chapter, you'll see how to determine the real last row of data in the worksheet, and you'll convert this constant to a module level variable.)

Const LastRow = 20

Using this value, you can verify that the row number is always in the range of 2 to LastRow, thus ensuring that the value in r always points to a valid row on the worksheet.

Remember that we have to handle the value of 1 as a special case because it's possible that the user has entered a 1 into the RowNumber text box as part of entering a number beginning with 1, such as 12 or 123. The easiest way to handle this issue is to simply clear the form by calling ClearData without issuing an error message.

Notice that the FormatNumber routine is used to convert the value in the first column to a number rather than simply assigning the value directly to the text box control. This technique ensures that the value is properly formatted in the field.

The same argument applies to the date data from the sixth column. The FormatDateTime function ensures that the data is properly formatted. While the function isn't specifically needed, it serves to remind you that you aren't dealing with text data.

Once the data is loaded onto the form, the Save and Cancel buttons are disabled by calling the DisableSave routine. These buttons are enabled only when the user changes a piece of information on the form.

Private Sub DisableSave()

CommandButton5.Enabled = False
CommandButton6.Enabled = False

End Sub

To hook the GetData routine into the form, switch from the code view of the user form to the object view showing the graphical representation of the form. Double-clicking the RowNumber control will take you back to the code view, but with one minor exception: the cursor will be placed in the middle of a new routine named RowNumber_Change.

Inside the new event, add a call to the GetData routine. This means that any time the data in the RowNumber control changes, the data shown in the form will be updated.

Private Sub RowNumber_Change()

GetData

End Sub

To test the routine, choose Run, Run Sub/UserForm from the main menu or press the F5 key. Then enter a row number into the RowNumber control. You will notice that the data from the appropriate row will be displayed. Also notice that it's impossible to enter a bad value for the row without generating an error message.

Navigating The Worksheet

Clicking any of the four navigation buttons should automatically adjust the value in the RowNumber text box. Then, because the value in RowNumber has been changed, the RowNumber_Change event will be fired and the currently displayed row will be updated.

Each of the four buttons represents a slightly different situation. The code for the First button is the simplest in that only a simple assignment statement is necessary to set RowNumber to 2. As with the RowNumber text box, the easiest way to edit the code for the appropriate event is to double-click the graphical control. The Visual Basic Editor will automatically add the event, and you can enter this line of code to complete it.

RowNumber.Text = "2"

**Tip: Test As You Go   **As you create the code for each button, take time to run the program and see the results. One of the strengths of Visual Basic is that you can quickly test your programs. It's far easier to debug five or 10 lines of code that you just added than to wait until you've added a few hundred lines of code.

The Prev and Next buttons are a little more complicated because you need to compute the value of the previous or next row based on the value of the current row. Like the GetData routine, this routine (shown in the following listing) begins by verifying that the value contained in RowNumber is numeric.

Private Sub CommandButton2_Click()

Dim r As Long

If IsNumeric(RowNumber.Text) Then
    r = CLng(RowNumber.Text)
    
    r = r ? 1
    If r > 1 And r <= LastRow Then
        RowNumber.Text = FormatNumber(r, 0)
        
    End If
    
End If

End Sub

Once the routine has a numeric value, it computes the new position by subtracting 1 (or adding 1 to find the next row). Finally, if the resulting row number is in the range of 2 to LastRow ?1, the value is saved into the RowNumber text box. The assignment will trigger the event for the RowNumber control, and the new information will be loaded.

Jumping to the last row is a bit more difficult because the concept of the last row is somewhat nebulous. After all, just because a worksheet can handle 65,536 rows of data doesn't mean that the user of that application wants to view rows that far down. Instead, it makes sense to look through the worksheet to find the last row with a value in the first column and treat that as the last row.

To make the last row dynamic, a few changes need to be made to the program. First the LastRow constant needs to be switched to a variable like this:

Private Sub UserForm_Initialize()

GetData

End Sub

If users want to see the last line in the form, they will need to press the Last button. There are several ways to locate the last row in response to the user clicking the Last button. One way would be to scan through all of the data looking for the first empty cell in column one each time the Last button was clicked.

A better way would be to scan through the worksheet and locate the first blank cell in column 1 and assign the value to LastRow, which is what the routine shown in the following listing does. This routine is located in the user form module.

Private Function FindLastRow()

Dim r As Long

r = 2
Do While r < 65536 And Len(Cells(r, 1).Text) > 0
    r = r + 1
    
Loop

FindLastRow = r

End Function 

The FindLastRow function scans through the worksheet to find the first cell that doesn't have a value. A simple While loop iterates through each cell in column one of the worksheet, and the length of the return value is tested to see if the length is zero. If the length is zero, the loop will end and r will point to the last row in the worksheet, which is the first blank row following the data in the worksheet.

Then you can set the LastRow variable by adding the following line to the UserForm_Initialize event.

LastRow = FindLastRow

The FindLastRow function can also be used in the event associated with the Last button to update the LastRow variable as well as set the value for the RowNumber control.

Private Sub CommandButton4_Click()

LastRow = FindLastRow - 1
RowNumber.Text = FormatNumber(LastRow, 0)

End Sub

Editing Data

At this point, you can view any row of data in the worksheet, but any changes you make in the data displayed on the form aren't saved in the worksheet. There are a lot of different techniques you can use, but here's one that should work well for you.

In this approach, the data displayed on the form is kept separate from the cells on the worksheet until the user explicitly presses either the Save or the Cancel button. Pressing the Save button should copy the data from the form to the worksheet, whereas pressing Cancel should reload the data from the worksheet, overwriting any changes in the user form that may have been made by the user. Both the Save and Cancel buttons should be disabled until the data on the form is actually changed.

The easiest way to disable these buttons is to set their Enabled property to False. Then change the Enabled property to True once one of the values in the field changes. You can reduce the amount of work by creating two subroutines, one named EnableSave and one named DisableSave, which enable and disable the command buttons associated with Save and Cancel, respectively. Then, in the Change event associated with the text boxes that contain data, add a call to the EnableSave subroutine. This setting means that any change to the data will mark the entire form as dirty, meaning that the data in the form is different from the data on the worksheet.

Because loading the data directly from the source means that the data is clean, the Save and Cancel buttons should call the DisableSave routine. This call should be placed only after the data is loaded onto the form because it's possible that the user might not have entered a valid row number and GetData might not actually reload any data.

The PutData routine found in the user form module (shown in the following listing) is similar to the GetData routine in that all the validations used to ensure that the value in RowNumber is valid are included. The main difference between the two routines is that the GetData routine copies information from the worksheet, whereas the PutData routine copies data to the worksheet.

Private Sub PutData()
        
Dim r As Long

If IsNumeric(RowNumber.Text) Then
        r = CLng(RowNumber.Text)
        
Else 
        MsgBox "Illegal row number"
        Exit Sub
        
End If

If r > 1 And r < LastRow Then
        Cells(r, 1) = CustomerId.Text
        Cells(r, 2) = CustomerName.Text
        Cells(r, 3) = City.Text
        Cells(r, 4) = State.Text
        Cells(r, 5) = Zip.Text
        Cells(r, 6) = DateAdded.Text
 
        DisableSave
        
Else
        MsgBox "Invalid row number"
        
End If

End Sub

The error checking isn't absolutely necessary, but it probably is a good idea just in case someone put an invalid value in the RowNumber text box, jumped to another application, and then came back. In that scenario, it's possible to enter a different value in the RowNumber text box without retrieving any data.

Notice that after the data is saved to the worksheet, the DisableSave routine is called. This is necessary because the data on the user form now represents the same data stored on the worksheet.

Adding Data

Pressing the Add button calls the CommandButton7_Click event, which displays the first blank row at the end of the worksheet. Because the LastRow variable points to this row, it's merely a matter of setting the Text property of the RowNumber control to this value using code like this:

Private Sub CommandButton7_Click()

RowNumber.Text = FormatNumber(LastRow, 0)

End Sub

Validating Data

At this point, the form is fully capable of capturing data from the user and inserting it into the worksheet. The form also allows the user to edit the values already stored in the worksheet. The only limitation is that none of the data is validated for correctness.

For instance, it's possible to enter an invalid date as part of the DateAdded field. Also, there are no checks to ensure that the CustomerId value is numeric. Finally, it's possible to enter the wrong two-character state code. Here are some techniques that you can use to ensure that the data is valid before it reaches your worksheet.

The first technique involves using the KeyPress event to ensure that the user can enter only a particular type of information. For example, you could ensure that the user can only enter numbers into the CustomerId control using this code:

Private Sub CustomerId_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

If KeyAscii < Asc("0") Or KeyAscii > Asc("9") Then
    KeyAscii = 0

End If

End Sub

**Tip: Defining Events   **Double-clicking the CustomerId control on the user form will automatically take you to the CustomerId_Change event. If the event doesn't exist, it will automatically be created. If you want to handle a different event, simply choose the name of the event from the drop-down list at the top of the code window and the Visual Basic Editor will automatically create a blank event with the appropriate parameters.

Another approach involves using the Exit event. In the Exit event associated with a particular control on the user form, you can determine if the user made an error and highlight the background to give the user a visual clue. You can also display a message box that contains a description of the error using code like this:

Private Sub DateAdded_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not IsDate(DateAdded.Text) Then
    DateAdded.BackColor = &HFF&
    MsgBox "Illegal date value"
    Cancel = True
    
Else
    DateAdded.BackColor = &H80000005
    
End If

End Sub

One nice feature of the Exit event is that if you set the Cancel argument to True, the user will be unable to switch the focus to a different control until the text box contains a proper date.

Remember that you also need to set the background color to Window Background (&H80000005) if there isn't an error, to reset any previous error conditions. This is handled by the Else clause.

The final technique used in this application prevents errors by substituting a combo box control in place of the text box control for State. Because the user is limited to choosing one value from the provided list of values, it becomes impossible to enter invalid data.

By setting the MatchRequired property of the combo box control to True, the user will be prevented from leaving the control until the input matches one of the values in the List. Another way to ensure that only a valid value is selected is to set the Style property of the combo box control to fmStyleDropDownList, which forces the control to operate as a list box, where the user can only choose a value from the specified list of values in the drop-down list instead of typing a value that might not be on the list.

In either case, a routine like the following AddStates routine is necessary to initialize the combo box control. This routine would typically be called from the user form's Initialize event.

Private Sub AddStates()

State.AddItem "AK"
State.AddItem "AL"
State.AddItem "AR"
State.AddItem "AZ"

End Sub

Displaying the User Form

The final step in this process is to create a simple macro that displays the user form. In this case, adding the following subroutine to the ThisWorkbook object in the Visual Basic Editor is all that's required to show the form. Any time the user wants to use this form, he simply has to run this macro.

Public Sub ShowForm()

UserForm1.Show vbModal

End Sub

As the vbModal value implies, once the form is displayed, it remains on the screen, preventing the user from accessing any part of the Excel worksheet underneath it. If it's important to provide this level of access, you can switch the vbModal value to vbModeless. Then the user will be able to switch between the form and the worksheet. (See Figure 20-4.)

Figure 20-4. The user can switch between the form and Excel if the form is displayed using vbModeless value.

**Warning   **Use the vbModeless value with care. In this example, allowing the user to access the underlying worksheet also permits the user to change the data that's currently displayed on the user form. If this happens and the user presses the Save button, any changes that the user might have made directly to the row will be lost.

Building a Multi-Step Wizard

The other example in this chapter shows you how to build a multi-step wizard that's invoked from the Excel 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.

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.

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