Chapter 6 - Using Scroll Bars in Excel.

       

     Microsoft Excel provides several controls for dialog sheets. You can use these controls on worksheets to help select data. For example, drop-down boxes, list boxes, spinners, and scroll bars are useful for selecting items from a list.

A scroll bar is used to interactively change the value of a cell and any cells or graphs that depend on the cell controlled by a scroll bar. This allows you to see how the results in a cell, chart, or graph change as the data changes. The following procedures demonstrate the use of drop-down boxes, list boxes, spinners, and scroll bars. The examples use the same list, cell link, and Index function.

To use the forms controls in Microsoft Office Excel 2007 and other new versions, you must enable the Developer tab. To do this, follow these steps:

1. Click the Microsoft Office Button , and then click Excel Options.

 

2 .In the Popular tab, click to select the Show Developer tab in the Ribbon check box, and then click OK.

Scroll bar

1. To add a scroll bar in Excel 2007 other new versions, click the Developer tab, click Insert, and then click Scroll Bar in the Form Controls section.

 

To add a scroll bar in Excel 2003 and in earlier versions of Excel, click the Scroll Bar button on the Forms tool-bar, and then create a scroll bar that covers cells in height and is about one-fourth of the width of the column. 

2.Right-click the scroll bars, and then click Format Control. Type the following information,and click OK:

1. In the Current value box, type 10.
   This initializes the scroll bar so the INDEX formula will point to the first item in the list.
2. In the Minimum value box, type 0.
   This value restricts the top of the scroll bar to the first item in the list.
3. In the Maximum value box, type 100. This number specifies the maximum number of entries in the list.
4. In the Incremental change box, type 1.
   This value controls how many numbers the scroll bar control increments the current value.
5. In the Page change box, type 10. This entry controls how much the current value will be incremented if you click inside the scroll bar on either side of the scroll box).
6. To put a number value in cell $A$4(depending on which item is selected in the list), type any of the cell index in the Cell link box.
   
   Click any cell so that the scroll bar is not selected. When you click the up or down control on the scroll bar, cell is updated to a number that indicates the current value of the scroll bar plus or minus the incremental change of the scroll bar. You can also drag the scroll box to change the value or click in the scroll bar on either side of the scroll box to increment it by 5 (the Page change value). The scroll bar will not change if the current value is 1 and you click the down control, or if the current value is 20 and you click the up control.

APPLIES TO

* Microsoft Office Excel 2007

* Microsoft Excel 2004 for Mac

* Microsoft Excel 2002 Standard Edition

* Microsoft Excel 2001 for Mac

* Microsoft Excel 2000 Standard Edition

* Microsoft Excel 98 for Macintosh

* Microsoft Excel 97 Standard Edition

* Microsoft Excel 2010

Creating Multiplication Table in Excel Using Scroll Bar.

1. Create a scroll bar linking one to cell C9.

2. Enter the values from 1 to 10 in the cells ‘A15’ to ’A24’. Enter the following formula”‘=$C$9*A15” in C15 (You can do this quickly by just typing the formula into B10 and then dragging it down. The $-signs act as absolute cell-references). Type titles for the table. In A9 type Multiplicand, in A13 type Multiplier and in B13 type Product.

3. Select the whole table, including titles, and click on the chart wizard: Choose the XY (Scatter) chart-type and select the sub type with smoothed lines without markers. Press Finish.

Snap Shot