Smart Scroll Bars in Excel

January 19th, 2010 by datapig Leave a reply »

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).

Sub AdjustMax()
ActiveSheet.ScrollBar1.Max = Range(“G2″).Value
ActiveSheet.ScrollBar2.Max = Range(“G3″).Value
ActiveSheet.ScrollBar3.Max = Range(“G4″).Value
ActiveSheet.ScrollBar4.Max = Range(“G5″).Value
End Sub

 

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.

Advertisement

10 comments

  1. Jeff Weir says:

    Really cool. I guess that makes you a (pardon the pun) control freak.

    I’d be tempted to add in-cell data bars on the right (using say =REPT(“l”,F2), so that you can easily see how the absolute values of each setting compares to the other.

  2. Rob van Gelder says:

    This is cool. Neat trick.

  3. JamesDX says:

    Anyone know how to do things like this?

  4. John says:

    I am trying to create a series of smart scroll bars for the following:

    I have a forecast model that has 60 months starting Jan 2012
    I would like a smart sliding date scroll bar that for 20 items that are dependant on each other.
    For example, the user would scroll and choose a date for item1. When the user chooses a date using the slider for item2, I need logic that would start item2′s slider 3 months later than the date value for item1. So, if item1 = Jan 2012, then the smart slider for Item2 would start Apr 2012. How would I go about doing this? This will continue until item20. Thanks in advance for any help.

  5. Andrew says:

    How can you use these scrollbars to navigate through columns?

    Cheers

  6. Kamil says:

    Let’s set the problem in a bit different way (into – dynamic change of scrollbar following cell change):
    If I change certain cell, I want my scrollbar to change too.

    Here’s short code for that:

    Private Sub ScrollBar1_Change()

  7. Kamil says:

    Ooops – my previous post was sent too quickly – apologies for that :)

    Private Sub ScrollBar1_Change()
    Dim lim_MIN, lim_MAX As Long
    Dim Cell_Value, Scroll_Value As Long

    ‘some numbers
    lim_MIN = 100
    lim_Max = 500000
    ScrollBar1.min = lim_MIN
    ScrollBar1.Max = lim_MAX
    ScrollBar1.LargeChange = 5
    ScrollBar1.SmallChange = 2

    ‘Let’s say the changed cell is B2
    Cell_Value = Range(“B2″).Value
    Scroll_Value = ScrollBar1.Value

    If Cell_Value Scroll_Value Then
    Scroll_Value = Cell_Value
    Else:
    Cell_Value = Scroll_Value
    End If

    End Sub

  8. T Urquhart says:

    I have set up some smart scroll bars and they work great. How can I set up multiples in different sheets within the same worksheet. It seems as though the scroll bar numbers reset on each sheet, therefore I am having difficulty differentiating scroll bar 1 from Sheet 1 from scroll bar 1 from Sheet 2. Do I need to set up another module or use a different Call name? please help.

    Thanks

  9. T Urquhart says:

    Nevermind, i have it working now

  10. Shivanand says:

    this is great and very simple to use!

Leave a Reply