User Forms

Excel provides us with UserForms as a means to provide a user interface (UI) to our Excel applications. UserForms are similar to Access or Visual Basic forms. They are containers for input and display controls. Both the forms and controls have properties, methods, and events that we can code against. Excel names new forms UserForm1, UserForm2, and so on, as they are added. They can be renamed as needed.

UserForms are inserted into your project by choosing Insert > UserForm or by right-clicking an object in the Project Explorer and choosing Insert > UserForm from the pop-up menu, as shown in Figure 1-33.

Figure 1-33. A new UserForm inserted in the VBE

When a new UserForm is inserted, the Toolbox window is also displayed. The Toolbox identifies the different controls that you can add to your forms.

Note If the Toolbox is not displayed, click View > Toolbox to display it.

The Toolbox (Figure 1-34) is customizable. The following are some of the customization options you have:

• Adding pages to the Toolbox

• Moving controls from one page to another

• Renaming pages

• Adding other controls, including ActiveX controls, to the Toolbox

• Copying customized controls from the form into the Toolbox

Tip The OK and Cancel buttons are special cases of a command button. If you add OK and Cancel templates to the Toolbox, you can quickly add them to other forms.

Toolbox ^TT

Controls I

Figure 1-34. The Toolbox window

Toolbox Window Elements

The Toolbox window contains the following controls:

Select Objects: This is the only item in the Toolbox that doesn't draw a control. When you select it, you can only resize or move a control that has already been placed on a form.

Label: This displays text that the user cannot change, such as a form heading.

TextBox: This allows entry or modification of text.

ComboBox: The ComboBox is a combination list box and text box. Users can either choose an item from the list or enter a value in the text box.

ListBox: This is used to display a list of items from which the user can choose. The list can be scrolled if it has more items than can be displayed at one time.

CheckBox: This creates a box that the user can click to select or deselect an item or to show a true or false value.

OptionButton: This displays multiple choices from which the user can choose only one.

ToggleButton: This button is used for toggling on and off.

Frame: This is a graphical or functional grouping for controls. To group controls, draw the frame first, and then place option buttons or check box controls inside the frame.

CommandButton: This creates a button the user can click to carry out a command.

TabStrip: This allows you to define multiple pages for the same area of a window or dialog box in your application.

MultiPage: This presents multiple screens of information as a single set.

ScrollBar: This provides a tool for quickly navigating through a long list of items or a large amount of information. It is also useful for indicating the current position on a scale, or as an input device or indicator of speed or quantity.

SpinButton: This is used in conjunction with another control to increment and decrement numbers. It can also be used to scroll back and forth through a range of values or a list of items.

Image: This displays an image from a graphics file on your form.

RefEdit: This allows the user to type or click and drag range references into its text area. It is similar to Excel's Set Print Area input function.

Figure 1-35 shows an example of an Excel 2007 UserForm.

Figure 1-35. Sample UserForm with controls added
0 0

Post a comment