If you want the control to run an existing macro, open the workbook that contains the macro.
Assign an existing macro- If you're adding a button, select the macro you want the button to run in the Macro name box.
-
If you're adding a control other than a button, right-click the control, and then click Assign Macro on the shortcut menu. Select the macro you want.
-
If the macro does not exist, click Record in the Assign Macro dialog box to record a new macro, or click New to open a Microsoft Visual Basic for Applications (VBA) module in which you can write the macro.
Right-click the control, click Edit Text, and then add the text for the control. When you're finished, right-click the control and then click Exit Edit Text.
Set control properties.
Right-click the control, click Format Control, and then click the Control tab. 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.
A control on a worksheet can run a macro only when the worksheet is active. If you want a button that's available from any workbook or worksheet, you can assign a macro to run from a toolbar button.