Page 425 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Creating User Forms

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. Chapter 19
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 set­
ting 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 exe­
cuted 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, call­
ing 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 bet€
ter 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
399