Objective 2.3: Create and Modify Custom Workbook Elements (δείγμα): 2.3.4. Insert and configure form controls

Objective 2.3: Create and Modify Custom Workbook Elements (δείγμα)

2.3.1. Create and modify cell styles
2.3.2. Create custom themes and theme elements
2.3.3. Create and modify simple macros
2.3.4. Insert and configure form controls

OBJECTIVE 2.3: Contents

2.3.4. Insert and configure form controls

A useful technique for ensuring accurate data entry is to add an extra worksheet layer that gives the user an interface for entering the data. This interface uses form controls such as lists, check boxes, and option buttons. These controls are linked to specific worksheet cells, and you associate certain values with these controls, which then ensure that the user can enter only the values you want. There are nine control types available.

The worksheet form controls are available in the Form Control gallery

The following table defines the available control types.

Icon	Name	Description
	Command button	When the user clicks this button, Excel launches a macro that you have assigned to it. When you add a command button to a worksheet, the Assign Macro dialog box opens, and from there you can either record a new macro or assign an existing macro.
	Combo box	This control displays a list from which the user can select an item. The control shows only one item at a time until it is expanded. Items in the list are defined by the values in a specified worksheet range, and the value returned to the linked cell is the number of the item chosen.
	Check box	This control offers an option that the user can switch on or off. On the worksheet, a selected check box stores the value TRUE in its linked cell; if the check box is cleared, it stores the value FALSE.
	Spin button	This type of button displays aη upward-pointing arrow and a downward-pointing arrow that, when clicked, increment or decrement (respectively) the value in a linked cell.
	List Box	This type of control displays a list from which the user can select an item. The items in the list are defined by the values in a specified worksheet range, and the value returned to the linked cell is the number of the item chosen.
	Option Button	When grouped with Other option buttons, a button of this type enables the user to select only one of the options. Option buttons work in tandem with group boxes (discussed next), in which the user can select only one of the option buttons within a group box.
	Group Box	This type of control is a grouping of two or more option buttons. The user can select only one option from the group.
	Label	A label control displays text that names or describes a worksheet form control.
	Scroll Bar	A scroll bar control resembles a window scroll bar. The user can use this type of scroll bar to select a number from a range of values. Dragging the scroll box changes the value of the control, and this value is what is returned to the linked cell. You can create either a horizontal or a vertical scroll bar.

To insert and configure a form control

1. On the Developer tab, in the Controls group, click Insert to display the Form Controls gallery.

2. Click the control you want to insert. The pointer changes to a crosshair.

3. Drag the pointer on the worksheet to create the control.

4. If you are creating a command button, in the Assign Macro dialog box, choose the macro you want to run when the user clicks the button.

Tip: To select a control (for example, to move it or resize it), hold down the Ctrl key and then click the control.

5. To edit the control caption (if any), right-click the control, click Edit Text, adjust the text accordingly, and then click outside the control.

6. Right-click the control, and then click Format Control to open the Format Control dialog box.

7. If the dialog box has a Control tab, click that tab, and in the Cell Link box, enter a reference for the control’s linked cell. Some controls (such as the group box and label) don’t link to a cell, so you won’t see this option.

Tip: When working with option buttons, you have to enter only the linked cell for one of the buttons in a group. Excel automatically adds the reference to the rest.

8. Configure other options for the control:

• Checked For check boxes and option buttons—click this option to display the control as selected.

• Unchecked For check boxes and option buttons—click this option to display the control as cleared.

• Input Range For list boxes and combo boxes—enter a reference to the worksheet range that contains the items you want to display in the list.

• Current Value For scroll bars and spin buttons—enter the initial value of the scroll bar or spin button.

• Minimum Value For scroll bars and spin buttons—for a scroll bar, enter the value when the scroll box is at its leftmost position (for a horizontal scroll bar) or its topmost position (for a vertical scroll bar); for a spin button, enter its smallest possible value.

• Maximum Value For scroll bars and spin buttons—for a scroll bar, enter the value when the scroll box is at its rightmost position (for a horizontal scroll bar) or its bottommost position (for a vertical scroll bar); for a spin button, enter its largest possible value.

• Incremental Change For scroll bars and spin buttons—for a scroll bar, enter the amount that the value changes when the user clicks on a scroll arrow; for a spin button, enter the amount the value changes when the user clicks an arrow.

• Page Change For scroll bars—enter the amount that the scroll bar’s value changes when the user clicks between the scroll box and a scroll arrow.

9. Click OK.