Writing Excel Macros with VBA, 2nd Edition

Chapter 14. Custom Dialog Boxes

As we have seen, Excel’s built-in dialogs offer very restricted communication with the user. Fortunately, Excel makes it possible to create custom dialog boxes that allow much more flexible communication. Custom dialog boxes are also called forms or UserForms . Our intention here is to present an introduction to the subject, which will provide a good jumping-off point for further study.

Generally speaking, most Excel applications will require only very simple forms. For example, we may want to display a form with a text box for text input, a list box to allow user selection, or some option buttons to select from several choices. Of course, we will want some command buttons to allow the user to execute procedures.

In fact, Microsoft’s Visual Basic is a more appropriate programming environment than Microsoft Office for creating applications that involve complex forms, since it was designed specifically for that purpose. And Visual Basic allows you to access any of the object models in the Microsoft Office suite, just as Excel does.

What Is a UserForm Object?

A UserForm object can be thought of as a standard code module with a visual interface (a form) that is used to interact with the user (hence the term UserForm). However, we must be careful not to take this description too literally. For instance, procedures (even public ones) that are declared in the General section of a UserForm module are generally intended to support objects (or code) ...