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

• Label: A label is an area of the form that may contain text. The text may be a permanent static part of the form, or may be changed dynamically by code written in VBA. If the name of the label object is LABEL1, the text comprising the label may be accessed as LABEL1.caption

• TextBox: A text box is an area of the form that may accept text from the user or may contain text provided by code written in VBA. If the name of the TextBox object is TEXTBOX1, the text in the TextBox may be accessed as TEXTBOX1.Value. The same text may also be accessed as TEXTBOX1.Text

• ListBox and ComboBox: A list box is an area of the form that displays a list of value from which the user may select a value. A combo box is similar to a list box, but in addition to allowing the user to select from a pre-defined list, the combo box allows the user to provide their own text, just as with a text box. A ListBox or ComboBox may have multiple columns. A list box, but not a combo box, may allow a user to select mulitple values by setting the property MultiSelect to True. In that case, the selected values must be identified by the property array Selected having a True at the corresponding index (counting from 0, not 1) with the value in the property array List. If only one value is possible then the property Value should be used.

• CheckBox, OptionButton (or RadioButton) and ToggleButton: Check boxes and buttons are used to turn some state one or off. They are like light switches that stay in the position you set them to (unlike a command button that acts like a doorbell or momentary contact switch). Option buttons may be given a group name, so that only one member of each group may be selected. The Value property is normally either True or False, but if neither True nor False has been selected, then Value Null is possible.

• CommandButton: A command button is an area of a form to generate a transient event to start something happening. The Click() event can be used as a means of responding to a button press

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:

• Start the VBA editor
• Create a form. Suppose the name of the form is UserForm1.
• Insert a procedure in a module:
  Public Sub ShowUF1()
    UserForm1.Show
  End Sub
  
• Return to Excel and create a command button hooked to ShowUF1