Flexible Interactive Workbooks in Excel

Tutorial 5

Add a spinner and scroll bar

Now to add some more functionallity.

This is where the forms toolbar is used. Adding a spinner to control the table reference and hence the conversion used, and a scroll bar to control the value to be converted, gives the workbook a more interactive feel.

The following steps will work through the stages described above.

1. Add a spinner

   • Click on the 'Spinner' button on the Forms toolbar, (fig. 11.), then click and drag on the worksheet to create the spinner.

   • Now right click the spinner and select 'Format control', (fig. 12.). Set the Minimum value to 1 and the Maximum value to 10. The Incremental change should be 1. Next, click in the Cell link box and select cell "G3" on the worksheet, (alternatively type G3 in the box), (fig. 13.).

   • You can now size and position the spinner as required. Right click, then left click the spinner to select it. Now you can move it and use the handles to resize it. It is best to position it so that it covers the cell it is controlling if this value is not to be seen.

2. Add a scroll bar

   • Click on the 'Scroll Bar' button on the Forms toolbar, then click and drag on the worksheet to create the scroll bar.

   • Now right click the scroll bar and select 'Format control'. Set the Minimum value to 0 and the Maximum value to 300 (you can change this again later if you wish). The Incremental change should be 1 and the Page change, 5. Next, click in the Cell link box and select cell "B4" on the worksheet, (alternatively type B4 in the box).

   • You can now size and position the scroll bar as with the spinner. Position it horizontally below the values.

Now move on to Step 6