Form Controls

You can add Form controls to your worksheets to make it easier to capture data.
These controls have been replaced by the ActiveX Controls and are really only made available for backwards compatibility reasons.
Having said that the Form controls are much easier to use because they only respond to a single predefined event, which is usually a mouse click.

There are 3 more (Text Field, Combo List and Combo Drop-Down) that can only be inserted onto "MS Excel 5.0 Dialog" worksheets.

Drawing a Control

When you hover over a control a screen tip will be displayed to identify the type of control.
As soon as you select a control your mouse pointer will change to a black cross allowing you to place the control.
When you place the control on the worksheet it will appear with a dotted line around it and will have eight white handles allowing you to resize it.

An alternative way to select a control is to use the "Select Objects" button on the Drawing toolbar and just drag the mouse over the control.
You can delete a control by pressing the Delete key when the control is selected.
You can create a default size control by just clicking on the worksheet.
What size are the controls at 80% ?

Renaming Controls

When you add controls the number appended to the end continues to increment regardless of whether controls are deleted.
The maximum number of controls you can add to a worksheet is 64000

Selecting a Control

Depending on the type of control you can only select the control when the cursor is displayed as a white cross. Not when then cursor is displayed as a hand.

Positioning a Control

If you want to align the control with the worksheet gridlines, hold down the Alt key as you draw the control with the mouse.
If you have already drawn the control, select it and hold down the Alt key as you drag the control.
You can drag any of the white boxes that appear on the corners and edges of the control
The edge or corner you drag will snap to the nearest gridline.

Using the Shortcut Menu

When a control is selected you can press the Right mouse button to display a shortcut menu.

Formatting your Control

The Format Control dialog box has four tabs although the first three are the same for all controls.
The Size, Protection and Properties tabs are all discussed in more detail on the Formatting a Control page later in this section.
The Control tab is probably the most important tab as its settings determine the data limits for the control and the cells for any input or output.
This settings on this tab vary depending on which control is selected.
You can link a text box to data in a cell. Select the textbox. Type in the formula bar "=A4" and press Enter ??

Display the (Format > Control) dialog box

A lot of these controls have a Cell Link field.
This can be useful if you want to item selected to be used directly in a worksheet, maybe as an argument to a calculation.

Label

There is no Control tab available for this control as no customisation is possible. If you want to change the text that is displayed in the label, then you can use the "Edit Text" command on the shortcut menu.
This is commonly used to provide a text description or label for other controls.

Group Box

If you want to change the text that is displayed at the top of the group box then you can use the "Edit Text" command on the shortcut menu.
This lets you group a number of option buttons together so they can all refer to the same linked cell.
Select the 3D shading check box to give the control a three-dimensional look.

Check Box

The Control tab allows you to set the default value of the check box and to link the response to an individual cell.
The control is linked to an individual cell which will contain True or False.
This cell could then be used within the IF function.
If you want to change the text that is displayed at the top of the group box then you can use the "Edit Text" command on the shortcut menu.
Can the cell link contain a named range ?

Option Button

The Control tab allows you to set the default value of the option button and to link the response to an individual cell.
Option buttons can be placed within Group Boxes to create multiple disjoint groups.
If you just add option buttons to a worksheet then all the buttons will belong to the same group.
All the option buttons in the same group will automatically have the same linked cell.
This linked cell will display a numerical value depending on which option button is selected.
If you want to change the text that is displayed at the top of the group box then you can use the "Edit Text" command on the shortcut menu.

List Box

The Control tab allows you to define the type of list box to use, the range of cells containing your list of data and an individual cell to contain the selected(s) item.
The value that is placed in the "Cell link" cell is a whole number and identifies the item in the list.
If you select the third item in the list then the number 3 is returned.
It is relatively easy to then obtain the actual value that was chosen by using the INDEX worksheet function.
Returns the number of the item selected in the list.

Combo Box

The drop-down lines text box is to allow you to change the height of the drop-down list.
The default is to display a maximum of eight possible values at any one time although this can be changed.
The value that is placed in the "Cell link" cell is a whole number and identifies the item in the list.
If you select the second item in the drop-down list then the number 2 is returned.
It is relatively easy to then obtain the actual value that was chosen by using the INDEX worksheet function.
Returns the number of the item selected in the list.

The cell linked can be any cell on any worksheet in any workbook.

Scroll Bar

The value that is placed in the "Cell link" cell is a whole number and
This control is just a scrollbar that allows for high and low limits as well as incremental changes.

Spinner

The value that is placed in the "Cell link" cell is a whole number and
A counter that allows for high and low limits as well as incremented change.
Basically a scrollbar without the scroll box between the arrows