Excel 2019 Bible

961

C H A P T E R

44

Creating UserForms

IN THIS CHAPTER

Understanding why you may want to create UserForms

Identifying UserForm alternatives

Creating UserForms: An overview

Looking at some UserForm examples

Finding more information on creating UserForms

Y

ou can’t use Excel very long without being exposed to dialog boxes. Excel, like most Windows

programs, uses dialog boxes to obtain information, clarify commands, and display messages. If

you develop VBA macros, you can create your own dialog boxes that work very much like those

that are built in to Excel. These dialog boxes are known as UserForms.

Understanding Why to Create UserForms

Some macros that you create behave the same every time that you execute them. For example, you

may develop a macro that enters a list of your sales regions into a worksheet range. This macro

always produces the same result and requires no additional user input. You may develop other mac-

ros, however, that perform differently under different circumstances or that offer options for the

user. In such cases, the macro may benefit from a custom dialog box.

The following is an example of a simple macro that makes each cell in the selected range uppercase

(but skips cells that have a formula). The procedure uses VBA’s built-in StrConv function:

Sub ChangeCase()

For Each cell In Selection

If Not cell.HasFormula Then

cell.Value = StrConv(cell.Value, vbUpperCase)

End If

Next Cell

End Sub

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

uppercase). This modification is not difficult to make, but if you make this change to the macro,

Excel® 2019 Bible, First Edition. Michael Alexander, Dick Kusleika and John Walkenbach.
© 2019 John Wiley & Sons, Inc. Published 2019 by John Wiley & Sons, Inc.