Page 409 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects

Chapter 18

Customizing Dialog Boxes

Displaying Existing Dialog Boxes . . . . . 384 Planning with Dialog Boxes . . . . . . . . . . 394
Modifying Existing Dialog Boxes . . . . . . 388

You don’t have to invent everything on your own when you can use features that already exist.
Microsoft Excel gives you access to most of the built-in dialogs within Excel and the other
applications in the Microsoft Office System 2003 Edition.
It’s true that your worksheet can be modified using the properties and methods supplied by
Visual Basic for Applications (VBA) code. However, if you have variables that the user wants
to be able to select while the macro is running, what options do you have? Your first option
is to design a User Form, as discussed in Chapter 19. The User Form can have the available
options listed, and the user could then select the appropriate settings. From the user’s selec­
tions, you can then apply her choices to the appropriate ranges within your workbook. Sure,
some of us delight in creating our own User Forms and using them whenever we can. How-
ever, typically there is training involved to have the user execute the macro to achieve the
results she has requested. User Forms require you to create everything from scratch, antici­
pating and programming every option you want to give to your users.

Your second option is to prompt the user for variable information using built-in dialog
boxes. For example, you can open the Border dialog box and apply the user’s selections to the
ranges you specify in your code. You can follow the same pattern with any dialog box that is
presented to the user. If the options the user has selected are applied to a blank workbook,
you can then extract the properties using VBA code and then apply those properties to the
appropriate ranges.
In general, when user intervention is required, your best option is to provide a dialog box
that users are already familiar with. You will find the training time is minimal when using
built-in dialog boxes.

The example macro provided in this chapter is specific to format changes; however, it cer­
tainly works with other variable changes, too. For example, if you want to prompt the user to
navigate to the folder where the workbook is saved, you can display the built-in dialog Save
As, using the following code:

Sub ShowSaveAs()
Application.Dialogs(xlDialogSaveAs).Show
End Sub

In this chapter, you will learn how to display built-in dialog boxes and manipulate them by
passing arguments to them and setting the dialog box properties.

383