|< Day Day Up >|| |
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 selections, 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. However, 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, anticipating 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 certainly 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:
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.
|< Day Day Up >|| |