Smart Scroll Bars in Excel

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.

14 thoughts on “Smart Scroll Bars in Excel

  1. Jeff Weir

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

    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.

  3. Kamil

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

  4. Kamil

    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
    Cell_Value = Scroll_Value
    End If

    End Sub

  5. T Urquhart

    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.


  6. Alexis

    Very nice indeed. I do have an issue while trying to use it though. I want to use your code to adjust the values of 4 variables inside a bigger program, but the values stored in the four cells from the previous run get used straightaway by the program before I have the time to make the change… How can I pause the main program until I made the change using your macro? Could I stop the main program until I click an extra button such as “OK” when I am done? How would I do that?

  7. Mohand Farid

    I need to add word table ( not just text to modify font color and size ) with specific size in excel with scroll bar as i will add 3 or 4 word pages of data per table

  8. Alberth

    Me parece muy genial y lo muy cercano a lo que necesito, y yo necesito algo mas alla de esto.
    Ej. un scrollbar mientras yo me desplazo de arriba-abajo o de ezquierda a derecha, otro scrollbar tambien lo haga pero de manera inversa al mismo tiempo, cuando este usando el primer scrollbar. Ambos deben de regresar un valor cualsea. Si alguien me puede apoyar, muy agradecido. Execelnte dia

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>