Using a cell value as the Max value in a scroll bar

[JO505]

I am using a scroll bar to select a list of dates for a simple chart. I would like to have the maximum value of the scroll bar be the same as the value in cell A2. That value comes from another sheet and is linked to a formula that returns the maximun value in a column on the second sheet. Is this possible and if it is how do I achieve this?

Jim O

[Palmetto]

Assuming you are using a control from Forms tool box:

Let's say A1 is the linked cell for the scroll bar. In another cell, say A3:

=MIN(A1:A2)

Replace the reference to A1 with that of A3.
Now, if the scroll bar is scrolled to a value higher than A2, your reference will always return the value of A2 which is the max you want to permit.

[JO505]

Palmetto,

Thanks for the input but that is not quiet what I was looking for.

I open the properties box and go to the "linked cell" line, the next line down is "locked" and then the next two lines are "Max" and "Min". Is their any way to link those values to a cell on the spreadsheet?

Jim O

[Palmetto]

I assumed you were using a control from the Forms Tool Box, but your scroll bar is from the Controls Tool Box and is an Active-X control.

Open the Control Tool Box, click the icon to enable Design Mode, then right-click the control and select View Code.

Paste this code in the VB Editor

Please Login or Register  to view this content.

This code dynamically sets the min/max values when the control gains focus.
Change the cell references as needed.

[JO505]

Palmetto,

That seems to work like a charm. It is just what I was hoping for. Thanks very much for your time.


Thank You!
Jim O

[Palmetto]

Glad to help.

[thejski]

Hello! Is it possible to do this exact same thing, but link the min and max from a different sheet?

[JeteMc]

Administrative Note:

Welcome to the forum thejski.

We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

Please see Forum Rule #4 about hijacking and start a new thread for your query.

If you are not familiar with how to start a new thread see the FAQ: How to start a new thread

Note that we ask for a general location (US, UK etc) as settings differ i.e. the use of semicolons ( ; ) or commas ( , ) for separators of function arguments. We also ask for the version of Excel as some functions are supported by later versions i.e. SUMIFS
Let us know if you have any questions.