Page 426 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Microsoft Office Excel 2003 Programming Inside Out

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
Chapter 19
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 work-
sheet 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 pro€
ductivity. 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 solu€
tions 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.

400