Excel 2016 Bible

Chapter 41Creating UserForms

IN THIS CHAPTER

1. Understanding why you may want to create UserForms
2. Identifying UserForm alternatives
3. Creating UserForms: An overview
4. Looking at some UserForm examples
5. Finding more information on creating UserForms

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