To display a UserForm from VBA, you create a procedure that uses the Show method of the UserForm object. You cannot display a UserForm 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:
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.
In addition, you can display a modeless UserForm. When a modeless UserForm is displayed, you can continue working in Excel, and the UserForm remains visible. By default, UserForms are displayed modally. (You must dismiss the UserForm before you can do anything else.) To display a modeless UserForm, use the following syntax:
Excel versions prior to Excel 2000 do not support modeless UserForms.
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 = "UserForml" UserForms.Add(MyForm).Show
This technique might be useful if your project contains several UserForms and the UserForm to be shown is determined by your code.
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:
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.
Was this article helpful?