Displaying a User Form

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.


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


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


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.

0 0

Post a comment