Visual Basic Controls

By Herbert J. Bernstein

© Copyright 2008 Herbert J. Bernstein

Visual Basic Forms are constructed using several "controls", small graphical interface elements that can be used to communicate with users. For many forms, the most important controls are labels, text boxes and buttons, that are used to provide information as text to users, accept information as text from users, and accept a request to perform an action from users, respectively.

Types of VBA controls

Hooking a VBA form to a spreadsheet

Normally a form is used for what is called a modal dialog. When the form is active, use of the rest of the application is blocked. This is particularly frustrating in working with spreadsheets, inasmuch as all calculations and preparations must be done before activating a form (usually with the Show method).

If you have VBA 6, you can set make a form non-modal (modeless) and the form can appear on a spreadsheet without blocking use of the spreadsheet.

However, a more common practice is to make use of the command button control offered by the Excel Forms toolbar. The steps are as follows:

The command button should have a caption or label to warn the user about what preparations are needed before pressing the button (e.g. selecting a range of cells). The form can access the active cell as a range as ActiveCell, the address of the active cell as ActiveCell.Address, and the user selection of cells as a range as ActiveWindow.RangeSelection.

When working with spreadsheet cells, note that the value in the active cell is ActiveCell.Value, while the formula in the active cell is ActiveCell.Formula.