Part VI: Programming Excel with VBA
838
This macro is useful, but it can be improved. For example, the macro would be more helpful if it
could also change the cells to lowercase or proper case (only the first letter of each word is upper-
case). This modification is not difficult to make, but if you make this change to the macro, you
need some method of asking the user what type of change to make to the cells. The solution is to
present a dialog box like the one shown in Figure 41.1. This dialog box is a UserForm that was
created by using the Visual Basic (VB) Editor, and it is displayed by a VBA macro.FIGURE 41.1A UserForm that asks the user to select an option.
Another solution is to develop three macros, one for each type of text case change. Combining
these three operations into a single macro and using a UserForm is a more efficient approach, how-
ever. I discuss this example, including how to create the UserForm, in “Another UserForm
Example,” later in the chapter.UserForm Alternatives
After you get the hang of it, developing UserForms isn’t difficult. But sometimes using the tools
that are built into VBA is easier. For example, VBA includes two functions (InputBox and
MsgBox) that enable you to display simple dialog boxes without having to create a UserForm in
the VB Editor. You can customize these dialog boxes in some ways, but they certainly don’t offer
the number of options that are available in a UserForm.The InputBox function
The InputBox function is useful for obtaining a single input from the user. A simplified version
of the function’s syntax follows:InputBox(prompt[,title][,default])The elements are defined as follows:l (^) prompt: (Required) Text that is displayed in the input box
l title: (Optional) Text that appears in the input box’s title bar
l (^) default: (Optional) The default value