Form Scroll Bar -- To link Minimum Value and Maximum Value to a Cell Reference (A1, B1) instead of a value (0, 100)

[DTan]

Hi all,

I know nothing about VBA or macros but I know that is the only way to go for this. Apologies but would appreciate if you explain it step-by-step for me to follow, beginning from launching VBA to the end.

I would need to create multiple form scroll bars within the same sheet.
Each scroll bar will have its minimum and maximum value linked to a cell reference (of a numerical vlookup value) respectively, also within the same sheet;
so for e.g. Scroll bar 1 will have its min. value = A1 and max. value = A2, where A1 and A2 are integers>0,
Scroll bar 2 will have its min. value = B1 and max. value = B2, and so on...

How do we go about doing this? Thanks in advance!

PS:Why can't excel allow users the option to key in the minimum and maximum values to cell references or whole numbers, that would make life much easier...

 

[mikerickson]

If you want a scroll bar to return a number between the value in B1 and C1:
Create a scroll bar, configure the controls min=0, max=100, linked to cell A1.

In D1, put =B1+(C1-B1)*(A1/100).

D1 will show the value (between B1 and C1) of the scroll bar.

 

[DTan]

Hi all,

I know nothing about VBA or macros but I know that is the only way to go for this. Apologies but would appreciate if you explain it step-by-step for me to follow, beginning from launching VBA to the end.

I would need to create multiple form scroll bars within the same sheet.
Each scroll bar will have its minimum and maximum value linked to a cell reference (of a numerical vlookup value) respectively, also within the same sheet;
so for e.g. Scroll bar 1 will have its min. value = A1 and max. value = A2, where A1 and A2 are integers>0,
Scroll bar 2 will have its min. value = B1 and max. value = B2, and so on...

How do we go about doing this? Thanks in advance!

PS:Why can't excel allow users the option to key in the minimum and maximum values to cell references or whole numbers, that would make life much easier...

For e.g.,
At one instant, the value in A1 is 1. The value in A2 is 3. The linked cell, which is say, A3, should be able to toggle between the numbers 1, 2 and 3 using the scroll bar.
In the next instant, the value in A1 changes to 4, and the value in A2 changes to 8. The linked cell A3 should be able to toggle between the numbers 4, 5, 6, 7 and 8 using the scroll bar.
That is what I require -- a dynamic scroll bar that will take reference from cells containing the dynamic maximum and minimum values, respectively.

 

[mikerickson]

The approach that I posted will do that.
Wrapping it in an INT would be an option.

 

[DTan]

The approach that I posted will do that.
Wrapping it in an INT would be an option.

Hi mikerickson,

That does not exactly work as intended. Because I would need the incremental increase to be 1 each time the user makes a click. So one click will mean increase by 1.

For e.g., let's say B1=1 and C1=3. Using the parameters of min 0, max 100, incremental change 1, we have
from your formula,

When A1=0 (default)
=B1+(C1-B1)*(A1/100)
=1+(3-1)*(0/100)
= 0

When A1=1 (first click)
=B1+(C1-B1)*(A1/100)
=1+(3-1)*(1/100)
= 1.02
= 1

When A1=2 (second click)
=B1+(C1-B1)*(A1/100)
=1+(3-1)*(2/100)
= 1.04
= 1 (when I would like it to be 2)

User would need a couple of clicks before 2 is reached, and so on...my intention is one click increase one number...

 

[mikerickson]

You could try this formula

=MAX(B1,MIN(C1,A1))

Where A1 is linked to the scroll bar, B1 is the minimum value, C1 the maximum.

You would want the control's format so that Maximum Value is set to your estimate of the maximum likely value entered in C1.