Keywords: Excel automation, VB controls, scrollbars
Version: Excel 97 or later
Let have a look at the VB controls in Excel. They can be quite handy when creating user oriented spreadsheets, or when building models to study relationships between parameters. In this article we will look at a simple scrollbar control.
1. For the purpose of this tutorial, create a new spreadsheet and in cell B4 enter “Level”, in cell C3 and D3 enter “A” and “B” respectively.
Picture 1
2. Bring up the Control toolbar by selecting View à Toolbars à Control Toolbox. The control toolbox should now be visible.
Picture 2
3. Select the scrollbar tool (see green arrow in picture 3). Click and drag somewhere in the worksheet to insert the scrollbar (see picture 3).
4. Right click on the scrollbar and select Properties. In the Properties window, find the LinkedCell property and enter cell reference C4 (see picture 3). This tells Excel that your scrollbar should communicate with cell C4.
Picture 3
5. A number will appear in cell C4, this represent the current value of the scrollbar control (see picture 4).
Picture 4
6. Select the scrollbar you just created, right click, select Copy, then select some cell (e.g. D7) and right click à Paste. You now have two identical scrollbars. Select the new scrollbar and follow the procedure in step 4 above, but set the LinkedCell to D4.
Picture 5
7. Close the properties windows, leave the Design mode by clicking the button shown by the arrow in picture 5.
8. Select cell range B3:D4. Click the Chart Wizard and insert a simple bar graph (see picture 6 and 7).
Picture 6
9. Now you can use the scrollbars to change the values in cell C4 and D4, and the chart will update dynamically.
Picture 7
This article was published on www.bloggpro.com.
Popularity: 3% [?]
