Change a control
Microsoft Excel has two different types of controls that you modify in different ways: ActiveX controls, and controls from the Forms toolbar.
- Determine which type of control you have: right-click the control you want to change.
If no shortcut menu appears, or the shortcut menu contains the command Properties, the control is an ActiveX control.
If the shortcut menu contains the command Assign Macro, the control is a Forms toolbar control.
- Based on the type of control you are working with, do one of the following:
- Display the Control Toolbox: point to Toolbars on the View menu, and then click Control Toolbox.
- Click Design Mode
. - Right-click the control, and then click Properties on the shortcut menu.
For more information about the properties, press F1 in the Properties dialog box to display the Microsoft Visual Basic Help for Microsoft Forms.
- When you finish modifying the control, click Exit Design Mode
.
Modify a Forms toolbar control- Click Format Control on the shortcut menu, and then click the Control tab.
- Set control properties.
Right-click the control, click Format Control, and then click the Control tab.
The following lists the Forms toolbar controls and their properties. Labels and buttons don't have properties.
Label Text that provides information about a control or the worksheet or form.
Edit box Unavailable in Microsoft Excel workbooks. This control is provided to allow you to work with Excel version 5.0 dialog sheets.
Group box Groups related controls, such as option buttons or check boxes.3D shading Displays the group box with a 3-dimensional shaded effect.
Button Runs a macro when clicked.
Check box Turns an option on or off. You can check more than one check box at a time on a sheet or in a group.Value Determines the state of the check box, that is, whether it is selected (Checked), cleared (Unchecked), or neither (Mixed).
Cell link A cell that returns the state of the check box. If the check box is selected, the cell in the Cell link box contains TRUE. If the check box is cleared, the cell contains FALSE. If the check box state is mixed, the cell contains #N/A. If the linked cell is empty, Excel interprets the check box state as FALSE.
3D shading Displays the check box with a 3-dimensional shaded effect.
Option button Selects one of a group of options contained in a group box. Use option buttons to allow only one of several possibilities.Value Determines the initial state of the option button, that is, whether it is selected (Checked) or cleared (Unchecked).
Cell Link Returns the number of the selected option button in the group of options (the first option button is number 1). Use the same Cell Link cell for all options in a group. You can then use the returned number in a formula or a macro to respond to the selected option.
For example, if you create a personnel form with an option button labeled Full-time and another option button labeled Part-time, you could link the two option buttons to cell C1. The following formula displays "Full-time" if the first option button is selected or "Part-time" if the second option button is selected:
=IF(C1=1,"Full-time","Part-time")
3D shading Displays the option button with a 3-dimensional shaded effect.
List box Displays a list of items.Input range Reference to the range containing the values to display in the list box.
Cell link Returns the number of the item that's selected in the list box (the first item in the list is 1). You can use this number in a formula or macro to return the actual item from the input range.
For example, if a list box is linked to cell C1 and the input range for the list is D10:D15, the following formula returns the value from range D10:D15 based on the selection in the list:
=INDEX(D10:D15,C1)
Selection type Specifies how items can be selected in the list. If you set the selection type to Multi or Extend, the cell specified in the Cell link box is ignored.
3D shading Displays the list box with a 3-dimensional shaded effect.
Combo box A drop-down list box. The item that is selected in the list box appears in the text box.Input range Reference to the range containing the values to display in the drop-down list.
Cell link Returns the number of the item that's selected in the combo box (the first item in the list is 1). You can use this number in a formula or macro to return the actual item from the input range.
For example, if a combo box is linked to cell C1 and the input range for the list is D10:D15, the following formula returns the value from range D10:D15 based on the selection in the list:
=INDEX(D10:D15,C1)
Drop-down lines Specifies the number of lines to display in the drop-down list.
3D shading Displays the combo box with a 3-dimensional shaded effect.
Combination list-edit Unavailable in Excel workbooks. This control is provided to allow you to work with Excel version 5.0 dialog sheets.
Combination drop-down edit Unavailable in Excel workbooks. This control is provided to allow you to work with Excel version 5.0 dialog sheets.
Scroll bar Scrolls through a range of values when you click the scroll arrows or when you drag a scroll box. You can move through a page of values by clicking between the scroll box and a scroll arrow.Current value The relative position of the scroll box within the scroll bar.
Minimum value The position of the scroll box closest to the top of a vertical scroll bar or the left end of a horizontal scroll bar.
Maximum value The position of the scroll box farthest from the top of a vertical scroll bar or the right end of a horizontal scroll bar.
Incremental change The amount the scroll box moves when the arrow at either end of the scroll bar is clicked.
Page change The amount the scroll box moves when you click between the scroll box and one of the scroll arrows.
Cell link Returns the current position of the scroll box. This number can be used in a formula or macro to respond to the position of the scroll box.
3D shading Displays the scroll bar with a 3-dimensional shaded effect.
Spinner Increases or decreases a value. To increase the value, click the up arrow; to decrease the value, click the down arrow.Current value The relative position of the spinner within the range of allowed values.
Minimum value The lowest value allowed for the spinner.
Maximum value The highest value allowed for the spinner.
Incremental change The amount the spinner increases or decreases when the arrows are clicked.
Cell link Returns the current position of the spinner. This number can be used in a formula or macro to return the actual value you want selected by the spinner.
3D shading Displays the spinner with a 3-dimensional shaded effect.
- To remove the selection handles when you're finished modifying the control, click a cell on the worksheet.