|< Day Day Up >|| |
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.
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.
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 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:
Create a new UserForm by choosing Insert, UserForm from the Visual Basic Editor's main menu.
Move the mouse pointer over the Label control in the toolbox.
Click and hold the mouse button down.
Move the mouse pointer onto the drawing surface and release the mouse button. This will add the Label control to the form.
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.
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.
Click the Close box to close the user form and return to the Visual Basic Editor.
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.
The UserForm contains a wide range of properties that control how it looks and works. Table 19-1 lists some of the key properties.
Contains the color of the background of the form.
Contains the value displayed in the form's title.
When True, means that the form is available for use.
Specifies the height of the form in points.
Specifies the distance between the left edge of the form and the left edge of the screen.
Contains the name of the form.
Displays the specified picture as the background of the form.
Specifies which scroll bars appear on the form. Can be: fmScrollBarsNone, fmScrollBarsHorizontal, fmScrollBarsVertical, or fmScrollBarsBoth.
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.
Specifies the distance between the top of the form and the top edge of the screen.
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.
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.
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:
You can show a modal form with the following statement:
However, if you call the Show method without specifying a mode, the form will be displayed as modal.
Forms and Command Bar Controls
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.
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.
|< Day Day Up >|| |