Chapter 19: Creating User Forms

---

User forms give Microsoft Excel programmers the opportunity to provide a different way for users to interact with their applications. In this chapter, you'll learn how to create a form and populate it with the standard controls supplied by Microsoft Visual Basic for Applications (VBA). Then you'll learn how to display and close forms from your own routines. Finally, you'll learn how to interact with the form from your VBA application.

Creating a UserForm

Visual Basic for Applications includes a special object known as a UserForm. A UserForm object provides a visible surface on which you can place graphical controls, such as buttons, images, and text areas.

Adding a UserForm

You can add a UserForm to your project by choosing Insert, UserForm from the Visual Basic Editor. (See Figure 19-1.) The user form consists of mockup of a window, including a title bar complete with the title UserForm1 and a Close button, plus a drawing surface filled with dots.

Figure 19-1: A UserForm object allows a VBA programmer to design a graphical interface to his application.

In addition to displaying the user form, the Visual Basic Editor also displays a floating toolbar called the Toolbox. The Toolbox contains various controls that can be placed on the drawing surface of the user form.

Designing a UserForm

Designing a UserForm involves selecting controls from the Toolbox and dragging them onto the drawing surface. Once they are on the form, you modify how they look by selecting the control and then working with it graphically or by going to the Properties window and modifying the control's properties.

To walk through a simple example of how this process works, just follow these steps:

1. Create a new UserForm by choosing Insert, UserForm from the Visual Basic Editor's main menu.

2. Move the mouse pointer over the Label control in the toolbox.

3. Click and hold the mouse button down.

4. Move the mouse pointer onto the drawing surface and release the mouse button. This will add the Label control to the form.

   

5. The text displayed in the Label control reflects the control's name, which in this case is Label1. You can change this value by changing the Caption property in the Properties window or by clicking the control and typing the new caption directly on the form.

6. You can test your new user form at any time by choosing Run, Run Sub/UserForm from the Visual Basic Editor's main menu. This will result in the Excel window being displayed with your new form displayed on top.

   

7. Click the Close box to close the user form and return to the Visual Basic Editor.

Modifying a UserForm

To modify a UserForm, you must first select it. You can select the user form by clicking anyplace on the form that doesn't have a control. You can also select the form by selecting it from the drop-down menu in the Properties window. When the form is selected, it will be displayed with a border of dots and squares.

Once you've selected the form, you can adjust its size by dragging its borders. Move the mouse pointer over one of the squares on the border, and the mouse pointer will change to a double arrow. Click and hold the mouse button while moving the mouse to adjust the size of the form.

You can also adjust the form's size in the Properties window. The height of the form is controlled by the Height property, and its width is controlled by the Width property. These values are measured in points.

Properties of a UserForm

The UserForm contains a wide range of properties that control how it looks and works. Table 19-1 lists some of the key properties.

Table 19-1: Key Properties of the UserForm Object

Property	Description
BackColor	Contains the color of the background of the form.
Caption	Contains the value displayed in the form's title.
Enabled	When True, means that the form is available for use.
Height	Specifies the height of the form in points.
Left	Specifies the distance between the left edge of the form and the left edge of the screen.
Name	Contains the name of the form.
Picture	Displays the specified picture as the background of the form.
ScrollBars	Specifies which scroll bars appear on the form. Can be: fmScrollBarsNone, fmScrollBarsHorizontal, fmScrollBarsVertical, or fmScrollBarsBoth.
StartUpPosition	Determines where the form is displayed on the screen. A value of 0 means that the Top and Left properties are used to position the form. A value of 1 means that the form will be displayed on the center of the Excel application. A value of 2 means that the form will be displayed in the center of the screen. A value of 3 means that the form will be displayed in the upper-left corner of the screen.
Top	Specifies the distance between the top of the form and the top edge of the screen.
Width	Specifies the width of the form in points.

The Name property contains the name of the user form. Remember that this property actually appears at the top of the list of properties rather than in alphabetical order like the rest of the properties are listed.

Adjusting the height and the width of the form graphically will automatically update the Height and Width properties. The Top and Left properties control the location of the form on the screen when the StartUpPosition property is set to 0.

The BackColor property allows you to change the color of the background of the form, whereas the Picture property lets you display a picture in the background. By default, these properties are set to use the settings from Windows. So if the user has a particular Windows theme installed, the form will use those colors.

Displaying a UserForm

User forms are just another object in Visual Basic, so they can be manipulated in code by setting properties, calling methods, and responding to events. You can easily create a macro that will display a form on the screen, and you can include code with the form that will be executed in response to various events (in programmer-speak this is called trapping events).

Displaying a form in VBA is a two-step process. First the form must be loaded, and then it must be shown. Loading a form allocates memory and initializes the form in preparation for showing it. Showing a form merely creates the graphical window that contains the form and makes it visible to the user.

You can load a form by calling the form's Load method, and you can display a form by calling the form's Show method. If the form isn't loaded when you call the Show method, it will be loaded for you automatically.

The opposite of Show is Hide, and the opposite of Load is Unload. So by calling the Hide method, you can remove a form from display without releasing its resources. Likewise, calling the Unload method will release all the resources associated with a form. Calling Unload while the form is visible on the screen will automatically remove the form from the display before releasing its resources.

Tip 

Faster Forms
Loading a user form can take a lot of resources. The more complex the form, the more resources it will take to load. If you plan to display the form quickly, you might want to hide the form, rather than unload it, to make your program run a little quicker. On the other hand, if you don't use a form very often, unloading it will save system resources that might be better used elsewhere.

There are two ways you can display a form: modal and modeless. When you display a modal form, all processing in the associated application stops until the form is closed. A message box is a good example of a modal form.

You can show a modeless form with the following statement:

UserForm1.Show vbModeless

You can show a modal form with the following statement:

UserForm1.Show vbModal 

However, if you call the Show method without specifying a mode, the form will be displayed as modal.

Tip 

Forms and Command Bar Controls
You might want to use the subroutine associated with the OnAction routine to call the Show method for a UserForm. This is a great way to start wizards or allow the user to input data into your application.

A modeless form does not block the application while the form is active. The application continues to operate as if the form were not present. This approach can be useful for many applications, but you should use modeless forms with care because they don't appear in the task bar, nor do they appear in the window tab order. This means that your users might lose a modeless form when they switch to another application or another window in the current application.

Associated with the life cycle of a form are three key events. The Activate event is triggered just before the form becomes the active window. The Deactivate event is triggered when the form is no longer the active window. The Terminate event is fired when the form is unloaded.

Remember that the Activate and Deactivate events are fired only as you move around in the VBA application. Switching to another application or switching from another application to Excel and your VBA program will not fire these events.

Inside Out
Built-in vs. Custom Dialog

You are probably wondering why you would want to use a dialog box to modify your worksheet when you can use the methods and properties supplied in VBA code. Well, there are times when you want the user to make the choice as to which color fill should be used or which font size is appropriate. This is the perfect example of when a dialog box should be used. It's much easier to show a built-in dialog box and allow users to select a color using a dialog box they are familiar with. After the color has been chosen, you can take their selections and apply them to the ranges you choose.

Using the built-in dialog box allows the user to interact with your code and still provide productivity. You can use an If Then…Else statement to apply a default color in case the user selects the Cancel button or has pressed the Esc key.

Remember, your code should work with the user. Your code should provide easy-to-use solutions that require little or no training to use. Using the built-in dialog boxes allows you to use an interface that the user is already comfortable with, so the user should require no training.

Before you create a custom dialog box, stop and evaluate if creating a dialog box is actually necessary. Is there a dialog box that exists that will save you the trouble? If so, try using the built-in dialog boxes and see if you are able to achieve your desired results.

---