Excel 2010 Bible

837

CHAPTER

Creating UserForms

IN THIS CHAPTER

Why create UserForms

UserForm alternatives

Creating UserForms: An

overview

UserForm examples

More 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.

Why Create UserForms?

Some macros that you create behave exactly 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

macros, 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 it skips cells that have a formula). The proce-

dure 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