Displaying and Closing UserForms

In this section, I provide an overview of using VBA to work with UserForms.

Displaying a UserForm

To display a UserForm from VBA, you create a procedure that uses the Show method of the UserForm object. You cannot display a UserForm from Excel without using at least one line of VBA code. If your UserForm is named UserForm1 , the following procedure displays the dialog box on that form:

 Sub ShowForm()     UserForm1.Show End Sub 

This procedure must be located in a standard VBA module and not in the code module for the UserForm.

When the UserForm is displayed, it remains visible onscreen until it is dismissed. Usually, you'll add a CommandButton to the UserForm that executes a procedure that dismisses the UserForm. The procedure can either unload the UserForm (with the Unload command) or hide the UserForm (with the Hide method of the UserForm object). This concept will become clearer as you work through various examples in this and subsequent chapters.

DISPLAYING A MODELESS USER FORM

By default, UserForms are displayed modally. This means that the UserForm must be dismissed before the user can do anything in the worksheet. You can also display a modeless UserForm. When a modeless UserForm is displayed, the user can continue working in Excel, and the UserForm remains visible. To display a modeless UserForm, use the following syntax:

 UserForm1.Show vbModeless 

DISPLAYING A USER FORM BASED ON A VARIABLE

In some cases, you may have several UserForms, and your code makes a decision regarding which of them to display. If the name of the UserForm is stored as a string variable, you can use the Add method to add the UserForm to the UserForms collection and then use the Show method of the UserForms collection. Here's an example that assigns the name of a UserForm to the MyForm variable and then displays the UserForm.

 MyForm = "UserForm1"     UserForms.Add(MyForm).Show 

LOADING A USERFORM

VBA also has a Load statement. Loading a UserForm loads it into memory, but it is not visible until you use the Show method. To load a UserForm, use a statement like this:

 Load UserForm1 

If you have a complex UserForm, you might want to load it into memory before it is needed so that it will appear more quickly when you use the Show method. In the majority of situations, however, it's not necessary to use the Load statement.

Closing a UserForm

To close a UserForm, use the Unload command, as shown in this example:

 Unload UserForm1 

Or, if the code is located in the code module for the UserForm, you can use the following:

 Unload Me 

In this case, the keyword Me refers to the UserForm. Using Me rather than the UserForm's name eliminates the need to modify your code if you change the name of the UserForm.

Normally, your VBA code should include the Unload command after the UserForm has performed its actions. For example, your UserForm may have a CommandButton that serves as an OK button. Clicking this button executes a macro. One of the statements in the macro will unload the UserForm. The UserForm remains visible on the screen until the macro that contains the Unload statement finishes.

When a UserForm is unloaded, its controls are reset to their original values. In other words, your code will not be able to access the user's choices after the UserForm is unloaded. If the user's choice must be used later on (after the UserForm is unloaded), you need to store the value in a Public variable, declared in a standard VBA module. Or you could store the value in a worksheet cell .

Note  

A UserForm is automatically unloaded when the user clicks the Close button (the — in the UserForm title bar). This action also triggers a UserForm QueryClose event, followed by a UserForm Terminate event.

UserForms also have a Hide method. When you invoke this method, the UserForm disappears, but it remains loaded in memory, so your code can still access the various properties of the controls. Here's an example of a statement that hides a UserForm:

 UserForm1.Hide 

Or, if the code is in the code module for the UserForm, you can use the following:

 Me.Hide 

If for some reason you would like your UserForm to disappear immediately while its macro is executing, use the Hide method at the top of the procedure. For example, in the following procedure, the UserForm disappears immediately when CommandButton1 is clicked. The last statement in the procedure unloads the UserForm.

 Private Sub CommandButton1_Click()     Me.Hide     For r = 1 To 10000         Cells(r, 1) = r     Next r     Unload Me End Sub 

CROSS-REFERENCE  

In Chapter 15, I describe how to display a progress indicator, which takes advantage of the fact that a UserForm remains visible while the macro executes.

About event handler procedures

After the UserForm is displayed, the user interacts with it - selecting an item from a ListBox, clicking a CommandButton, and so on. In official terminology, the user causes an event to occur. For example, clicking a CommandButton causes the Click event for the CommandButton. You need to write procedures that execute when these events occur. These procedures are sometimes known as event handler procedures.

Note  

Event handler procedures must be located in the Code window for the UserForm. However, your event handler procedure can call another procedure that's located in a standard VBA module.

Your VBA code can change the properties of the controls while the UserForm is displayed (that is, at runtime). For example, you could assign to a ListBox control a procedure that changes the text in a Label when an item is selected. This type of manipulation will become clearer later in this chapter.