Preliminary Words About Control Objects

The Excel application we are building here is designed to collect user input, process that input, and provide the user with information. Information is presented to the user based upon what the user does with various control objects, such as selecting from a list box or selecting a particular option button, or by clicking a button.

The control objects we will use will be drawn from the Forms toolbar. Additional explanation of control objects and their use and coding is presented in the main course, Chapter 5, Buttons DropDowns and Other Controls (Introduction to Controls). We will place the controls on the Main worksheet. This worksheet will serve as the principal user interface for the Trip Planner application.

The controls we are using are list boxes, option buttons, text boxes, and buttons (command buttons in Active X control terminology). Neither dropdown boxes (also called Combo boxes) nor check boxes are covered here. A dropdown is handled the same as a list box in most cases, except no multi-selection occurs with a dropdown box. Check boxes are handled like option buttons only more than one check box can be selected at the same time. All these controls are explained in Chapter 5 Introduction to Controls.

Formatting and assigning macros to controls

Formatting and assigning macros to controls is done by right-clicking on a control. When you do this, a menu shows allowing you to format the control or to assign a macro to it. When you choose Format Control, a Format Control dialog shows up with several tabs. We will use this tool frequently during the project.

Naming controls

Controls are named by a three step process:

Selecting the control. The control is automatically selected right after you create it on the page. You can also select the control by right-clicking it or by holding down the Ctrl key while left-clicking the control.
Naming the control in the name box (see figure below).
Pressing Enter after naming the control. Pressing Enter is imperative to successfully naming a control. If you do not press Enter, the control will not be named.

Aligning Controls

An Excel application looks more professional if the controls are lined up and spaced evenly. Excel provides Align and Distribute tools for accomplishing this. These tools are available in the Drawing menu on the Drawing toolbar, or in your own custom Align toolbar if you made one (see Make and Use a Custom Align Toolbar).

In order to align controls, follow this procedure (also discussed in previous link):

Hold down the Shift + Ctrl keys
Left-click all controls you want to align or distribute (space evenly)
Click the appropriate Align or Distribute button

Note that you can only select one list box at a time when you are selecting multiple objects, and then, you have to select the list box first. I am not sure why this is. However, I wrote some code that allows you to select multiple list boxes on a sheet. We will use this code later.

VBATrain Home | Samples TOC | Previous | Next