UserForms Basics

A UserForm is useful if your VBA macro needs to get information from a user. For example, your macro may have some options that can be specified in a UserForm. If only a few pieces of information are required (for example, a Yes/No answer or a text string), one of the techniques I describe in previous articles may do the job. But if you need to obtain more information, you must create a UserForm.

To create a UserForm, you usually take the following general steps:

1. Determine how the dialog box will be used and where it will be displayed in your VBA macro.
2. Activate the VBE and insert a new UserForm object. A UserForm object holds a single UserForm.
3. Add controls to the UserForm. Controls include items such as text boxes, buttons, check boxes, and list boxes.
4. Use the Properties window to modify the properties for the controls or for the UserForm itself.
5. Write event-handler procedures for the controls (for example, a macro that executes when the user clicks a button in the dialog box). These procedures are stored in the Code window for the UserForm object.
6. Write a procedure (stored in a VBA module) that displays the dialog box to the user.

Don’t worry if some of these steps seem foreign. I provide more details in the following sections, along with step-by-step instructions for creating a UserForm.

When you are designing a UserForm, you are creating what developers call the Graphical User Interface (GUI) to your application. Take some time to consider what your form should look like and how your users are likely to want to interact with the elements on the UserForm. Try to guide them through the steps they need to take on the form by carefully considering the arrangement and wording of the controls. Like most things VBA-related, the more you do it, the easier it gets.