Add a scroll bar or spin button to a worksheet

Add a scroll bar (Form control)

1. On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Scroll bar .

   

2. Click the worksheet location where you want the upper-left corner of the scroll bar to appear.

   Note: The scroll bar is added in a top-down orientation.

3. To orient the scroll bar from left to right, drag one of the sizing handles in a diagonal direction.

4. On the Developer tab, in the Controls group, click Properties .

   Tip: You can also right-click the control, and then click Format Control.

   Note: Before you click Properties, make sure that the object for which you want to examine or change properties is already selected.

   To specify the control properties, do the following:

   1. In the Current value box, enter the initial value in the range of allowed values below that corresponds to the position of the scroll box in the scroll bar. This value must not be:

      • Less than the Minimum value; otherwise, the Minimum value is used.

      • Greater than the Maximum value; otherwise, the Maximum value is used.

   2. In the Minimum value box, enter the lowest value that a user can specify by positioning the scroll box closest to the top of a vertical scroll bar or the left end of a horizontal scroll bar.

   3. In the Maximum value box, enter the largest value that a user can specify by positioning the scroll box farthest from the top of a vertical scroll bar or the right end of a horizontal scroll bar.

   4. In the Incremental change box, enter the amount that the value increases or decreases and the degree to which the scroll box moves when the arrow at either end of the scroll bar is clicked.

   5. In the Page change box, enter the amount that the value increases or decreases and the degree to which the scroll box moves when you click the area between the scroll box and either of the scroll arrows. For example, in a scroll box with a minimum value of 0 and a maximum value of 10, if you set the Page change property to 2, the value will increase or decrease by 2 (in this case, 20% of the value range of the scroll box) when you click the area between the scroll box and either of the scroll arrows.

   6. In the Cell link box, enter a cell reference that contains the current position of the scroll box.

      The linked cell returns the current value corresponding to the position of the scroll box.

      Use this value in a formula to respond to the value of the cell specified in the Cell link box that corresponds to the current position of the scroll box. For example, if you create a risk factor scroll bar with the following properties:

With these settings, the user can use the scroll bar to enter a precise number or click the area between the scroll bar and arrow to change the value in increments of 5.

The following formula in cell D1 returns the exact value that is based on the current value in the linked cell:

=IF(C1 > 50, "Acceptable", "Unacceptable")

The following array formula in cell D1 assigns a grade to the risk factor, based on the current value in the linked cell.

=LOOKUP(A4,{0,20,40,60,80},{"F","D","C","B","A"})

Add a scroll bar (ActiveX control)

1. On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Scroll Bar .

   

2. Click the worksheet location where you want the upper-left corner of the scroll bar to appear.

3. To edit the control, make sure that you are in design mode. On the Developer tab, in the Controls group, click Design Mode .

4. To specify the control properties, on the Developer tab, in the Controls group, click Properties .

   Tip: You can also right-click the control, and then click Properties.

   The Properties dialog box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available.

   Summary of properties by functional categories

Add a spin button (Form control)

1. On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Spin Button .

   

2. Click the worksheet location where you want the upper-left corner of the spin button to appear.

3. On the Developer tab, in the Controls group, click Properties .

   Tip: You can also right-click the control, and then click Format Control.

   To set the control properties, do the following:

   1. In the Current value box, enter the initial value of the spin button within the range of allowed values below. This value must not be:

      • Less than the Minimum value, otherwise the Minimum value is used.

      • Greater than the Maximum value, otherwise the Maximum value is used.

   2. In the Minimum value box, enter the lowest value that a user can specify by clicking the bottom arrow in the spin button.

   3. In the Maximum value box, enter the highest value that a user can specify by clicking the top arrow in the spin button.

   4. In the Incremental change box, enter the amount that the value increases or decreases when the arrows are clicked.

   5. In the Cell link box, enter a cell reference that contains the current position of the spin button.

      The linked cell returns the current position of the spin button.

      Use this value in a formula to respond to value of the cell specified in the Cell link box that corresponds to the current position of the spin button. For example, you create a spin button for setting the current age of an employee with the following properties:

With these settings, the user can click the spin button to enter an age that falls within a minimum and maximum age range. The median age of the employees is 35, and therefore 35 is a good choice to set as the starting value.

The following formula in cell D1 determines the length of employment that is based on the current age value in the cell that is linked to the spin button and the employee's age at hire date — the value in B1 (retrieved from another data source). The formula then computes a holiday bonus percentage that is based on years of service:

=(C1 - B1)* .01

Add a spin button (ActiveX control)

1. On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Spin Button .

   

2. Click the worksheet location where you want the upper-left corner of the spin button to appear.

3. To edit the control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode .

4. To specify the control properties, on the Developer tab, in the Controls group, click Properties .

   Tip: You can also right-click the control, and then click Properties.

   The Properties dialog box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help topic. You can also type the property name in the Visual Basic Help Search box. The following section summarizes the properties that are available.

   Summary of properties by functional categories