Microsoft Office Tutorials and References
In Depth Information
Part 5: Manipulating Excel Objects
Creating Advanced User Forms
Capturing Information. . . . . . . . . . . . . . 419
Building a Multi-Step Wizard . . . . . . . . . 431
User forms are a critical part of Microsoft Excel programming in that they provide a surface
that is totally under your control with which you can interact with a user. This makes it pos€
sible to build more complex Excel applications. Forms can also be used to collect and verify
information from a user before it’s entered into a worksheet. They can also be used as part of
an add-in to display options and control execution of a particular task. In this chapter, you’ll
learn how to build a user form that allows a user to input data into the worksheet, plus how
to build an add-in that invokes a multi-step wizard that creates a chart based on selections
made by a user.
Entering information into a worksheet can be painful sometimes. It’s difficult to ensure that
the data is valid and is properly formatted. It can also be difficult to ensure that the data is
added at the proper location. In this example, you’ll learn how to construct a simple form
that accepts data from the user and stores it in a worksheet.
Form Application Overview
The Excel worksheet used in this sample application merely records six pieces of information
about a customer at The Garden Company: CustomerId, Name, City, State, ZipCode, and
DateAdded. (See Figure 20-1.)
This form is started by running a macro and remains up until the user explicitly closes the
form. Buttons on the form control which row of the worksheet is displayed through the form,
and the form itself allows the user to enter or edit any data stored in a particular row.
On the CD The complete source code for this example is found on the Companion CD for
this book in FormApp.xls. Rather than entering each code listing found in this chapter, you
should load the sample file, which includes some sample data you can use for testing.