Show All

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.

1. 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.

2. Based on the type of control you are working with, do one of the following:

   Modify an ActiveX control

   1. Display the Control Toolbox: point to Toolbars on the View menu, and then click Control Toolbox.
   2. Click Design Mode .
   3. 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.

   4. When you finish modifying the control, click Exit Design Mode .

   Modify a Forms toolbar control

   1. Click Format Control on the shortcut menu, and then click the Control tab.
   2. Set control properties.

      How?

      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.

      Group box properties

      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.

      Check box properties

      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.

      Option button properties

      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.

      List box properties

      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.

      Combo box properties

      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.

      Scroll bar properties

      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.

      Spinner properties

      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.

   3. To remove the selection handles when you're finished modifying the control, click a cell on the worksheet.