It's time for another life-changing Excel trick. In this post, I'll show you how to make what I call "smart Scroll Bars". These are Scroll Bars that work together to dynamically change their max parameters based on a predefined limit. I put together this snazzy flash animation to show you what I mean.
In this scenario, the total allowed is 100. This means that the total value output by all these Scroll Bars cannot exceed 100. As each Scroll Bar is adjusted, the others will dynamically reset their max limits to equal the balance. This, in effect, makes the Scroll Bars work together to avoid going over the limit.
Here are the basic steps for creating smart Scroll Bars:
Step 1: Set up your spreadsheet.
The basic set up relatively simple. You first provide for a Values column where the Scroll Bars will output their values.
Then you specify a "Total Allowed" field which will hold your maximum limit.
From there, you included a formula to calculate the "Balance" (F6), defining the difference between the sum of all the values and the Total Allowed.
The Balance is then used in the Max column to dynamically calculate the Max limit for each Scroll Bar.
Step 2: Adding your Scroll Bars.
The next step is to add some Scroll Bars. In this scenario, we'll have to use ActiveX Scroll Bar controls - not Form controls. This is because Form controls don't provide a scroll event. We need to dynamically alter the properties of the Scroll Bars when the Scroll Bar is clicked or scrolled.
In Excel 2007, you can find the ActiveX Scroll Bar control under the Insert dropdown on the Developer tab.
In Excel 2003, you'll need to click View -> Toolbars -> Control Toolbox. This will activate the Control Toolbox, where you will find the Scroll Bar control.
When you find them, just plop as many as you need onto your spreadsheet. In this scenario, I'll need four.
Step 3: Define the Output Cells.
Once you have your Scroll Bars on your spreadsheet, right click each one and click Properties. This will activate the Properties dialog box. Here, you'll need to define the output cell for each Scroll Bar. This particular Scroll Bar will output to F2 (in the Values column). Remember, you'll need to do this for all your Scroll Bars.
Step 4: Create the AdjustMax Macro.
Now we'll need to get a bit of code going. Press Alt+F11 to get to your VBE editor. Once you're in the VBE, start a new module (Insert -> Module).
Insert this code in your new module. As this code suggests, this macro will dynamically reset the Max property for each of the Scroll Bars to the values in Column G (my Max column).
ActiveSheet.ScrollBar1.Max = Range("G2").Value
ActiveSheet.ScrollBar2.Max = Range("G3").Value
ActiveSheet.ScrollBar3.Max = Range("G4").Value
ActiveSheet.ScrollBar4.Max = Range("G5").Value
By the way, if you're wondering why we can't set the Max properties of a Scroll Bar using a simple cell reference, join the club. This has been a common request for many years. Maybe Excel 15, huh Microsoft?
Step 5: Add Event Trigger to the Scroll Bars.
The last step is to add some event triggers to our Scroll Bars so that the AdjustMax macro fires when the Scroll Bars are clicked or scrolled. You do this by double-clicking each Scroll Bar. When you double-click an ActiveX control, you're taken to its "code-behind". Here, you can build your event triggers.
For each of our Scroll Bars, we'll add three events: Change, GotFocus, and Scroll. Here, you can see that I'm simply calling the AdjustMax macro on each of these events.
At this point, you're basically done; apart from some formatting.
Just like one of those cooking shows, I've got a sample already cooked and ready for you to try. Download here.
Admittedly, smart Scroll Bars are a bit cumbersome to set up, but they definitely come in handy in complex data models and applications where data limits are important.