Using a Form in a Procedure

After you've created your form, the next step is to incorporate your handiwork into some VBA code. This involves three separate techniques:

Displaying the form
Handling events while the form is displayed
Processing the form results

Displaying the Form

Each UserForm object has a Show method that you use to display the form to the user. For example, to display a form named UserForm1, you would use the following statement:

UserForm1.Show

Alternatively, you may want to load the form into memory but keep it hidden from the user. For example, you may need to perform some behind-the-scenes manipulation of the form before showing it to the user. You can do this by executing the Load statement:

Load Form

Form
The name of the form you want to load.

This statement brings the form object into memory and fires the form's Initialize event. From there, you can display the form to the user at any time by running the form's Show method as discussed earlier.

Before getting to the code stage, you might want to try out your form to make sure it looks okay.To do this, activate the form and then select Run, Run Sub/UserForm, or press F5, or click the Run Sub/UserForm button on the toolbar.

Unloading the Form

After the user has filled out the form, you'll probably want to include on the form a command button to put whatever values the user entered into effect. Alternatively, the user could click some sort of Cancel button to dismiss the form without affecting anything.

However, just clicking a command button doesn't get rid of the form-even if you've set up a command button with the Default or Cancel property set to True. Instead, you have to add the following statement to the event handler for the command button:

Unload Me

The Unload command tells VBA to dismiss the form. Note that the Me keyword refers to the form in which the event handler resides. For example, the following event handler processes a click on a command button named cmdCancel:

Private Sub cmdCancel_Click()
    Dim result as Integer
    result = MsgBox("Are you sure you want to Cancel?", _
                    vbYesNo + vbQuestion)
    If result = vbYes Then Unload Me
End Sub

You should note, however, that simply unloading a form doesn't remove the form object from memory. To ensure proper cleanup (technically, to ensure that the form object class fires its internal Terminate event), Set the form object to Nothing. For example, the following two lines Show the TabStripTest form and then Set it to Nothing to ensure termination:

TabStripTest.Show
Set TabStripTest = Nothing

Processing the Form ResultsM

When the user clicks OK or Cancel (or any other control that includes the Unload Me statement in its Click event handler), you usually need to examine the form results and process them in some way.

Obviously, how you proceed depends on whether the user has clicked OK or Cancel because this almost always determines whether the other form selections should be accepted or ignored.

If OK is clicked, the Click event handler for that button can process the results. In other words, it can read the Value property for each control (for example, by storing them in variables for later use in the program).
If Cancel is clicked, the code can move on without processing the results. (As shown earlier, you can include code to ask the user whether he's sure he wants to cancel.)

Table shows lists all the controls that have a Value property and provides a description of what kind of data gets returned.

Value Properties for Some Form Controls

Object	What it Return
CheckBox	True if the check box is activated; False if it's deactivated; Null otherwise.
ComboBox	The position of the selected item in the list (where 1 is the first item).
ListBox	The position of the selected item in the list (where 1 is the first item).
MultiPage	An integer that represents the active page (where 0 is the first page).
OptionButton	True if the option is activated; False if it's deactivated; Null otherwise.
ScrollBar	A number between the scrollbar's minimum and maximum values.
SpinButton	A number between the spinner's minimum and maximum values.
TabStrip	An integer that represents the active tab (where 0 is the first tab).
TextBox	The value entered in the box
ToggleButton	True if the button is pressed; False otherwise.

Tip: If you've set up your list box to accept multiple selections, simply checking the Value property won't work. Instead, you need to run through all the items in the list and check each item to see whether its Selected property is True. If a list item is selected, then you use the corresponding item in the List array to return the value of the selected item.The following code demonstrates this technique by using the form's Terminate event (fired when the form is closed) to store the selected list values in a worksheet:

Private Sub UserForm_Terminate()
    Dim nItem As Integer
    Dim nSelected As Integer
    nSelected = 0
    For nItem = 0 To ListBox1.ListCount - 1
        If ListBox1.Selected(nItem) Then
            ActiveSheet.Range("A1").Offset(nSelected, 0) =
ListBox1.List(nItem)
            nSelected = nSelected + 1
        End If
    Next 'i
End Sub

[Previous] [Contents]

Using a Form in a Procedure

Displaying the Form

Unloading the Form

Processing the Form ResultsM

In this tutorial: