Scroll Bars - Negatives & Decimals

Ever wish you could choose a different increment than a positive whole number?  With VBA you can accomplish both and far more.

In http://automatenow.blogspot.com/2011/01/scroll-bars-financial-modelling.html I introduced how scrolling bars were able to affect a graph in real time by linking that scroll bar to a cell and allowing the chart and corresponding graph to update based on the values.  The limitation is that you can only scroll from 0 - 30,000.

One way you can work around these limitations is by using VBA to create a value in one (or more) cells outside of the linked as shown here:

Although the value in cell A1 still changes by increments of 1 cell A2 can now be affected by any change in the very same scroll bar.  This is done by using the change method to the object scrollbar1.

Now, with the code above, any change in the scroll bar will launch the script to update cells A2 with the formula defined in that script with cell A1 - 50 * 0.25.  Move the scroll bar to 50 and the value in A2 becomes your new zero.  Move it to the left and you will see it progress down by 0.25 increments in the negative direction!

You can clean this worksheet up by dragging the scroll bar to cover up your linked cell (A1) to allow your user to only see the resulting -1.75 when the scroll bar value is actually 43.  Imagine now how easy it is to have several cells to apply complex formulas, functions or even text results driven by this scroll bar...