Leading the way in Microsoft Office Development

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.

Button

There is no Control tab available for this control although any specific customisation can be done via the "Margins" tab. All the other tabs are standard.

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.

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.

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 ??

Things to Remember

You can link one cell to several controls but a control can only be linked to a single cell.

When a control changes the value in a cell any formulas referring to that cell will be automatically recalculated (manual or automatic ??).