Formula or VBA for a Scroll bar to match min and max values of a variable cell?

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
512
Hi everyone,

In a table i have :
E1= Apple; F1= 0.2%; G1= 1.5%
E2= Banana; F2= 1.5%; G2= 3%
E3= Pear; F3= 1%; G3= 4%
E4= Cherry; F4= 4% ; G4= 10%
E5= Peach; F5= 5% ; G5= 20%

Column F represents the minimum % and column G represents maximum %

In A1 i have a data validation drop down list E1:E5 (Apple,Banana;Pear,Cherry,Peach)

Whatever selection of A1 value, i need a scroll bar form linked to cell "C1" to automatically set the minimum & the maximum values matched in F1:G5

Ex: If A1= "Pear" the scroll bar should scroll down C1 to a min of 1% and scroll up a max of 4%
If A1= "Peach" it should scroll down C1 to a min of 5% and scroll up a max of 20%

Could that be done with a formula? cause i dont know VBA, if so would you kindly find the easiest VBA code with instructions please?

I appreciate very much your support !
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
650
Hi,

Does this suit?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Peach</td><td style="text-align: right;;"></td><td style="text-align: right;;">20.0%</td><td style="text-align: right;;"></td><td style=";">Fruits</td><td style=";">Min</td><td style=";">Max</td><td style=";">DV</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Apple</td><td style="text-align: right;;">0.2%</td><td style="text-align: right;;">2%</td><td style="text-align: right;;">0.05</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Banana</td><td style="text-align: right;;">1.5%</td><td style="text-align: right;;">3%</td><td style="text-align: right;;">0.2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Pear</td><td style="text-align: right;;">1%</td><td style="text-align: right;;">4%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Cheery</td><td style="text-align: right;;">4%</td><td style="text-align: right;;">10%</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Peach</td><td style="text-align: right;;">5%</td><td style="text-align: right;;">20%</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A$1,$E$2:$G$6,ROWS(<font color="Red">$H$1:H2</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A$1,$E$2:$G$6,ROWS(<font color="Red">$H$1:H3</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
512
Thank you for your reply.
Your formulas lookup the min and max and returns it in cells, what i need exactly is that the scroll bar linked to C1 to be set to a max of H3 and minimum of H2 meaning that the scroll bar should not scroll down to less than 5% and not to scroll up more than 20 % as in your Peach example.

Sorry the scroll bar was not generated by the html maker, it's between C7 and B7

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Peach</td><td style="text-align: right;;"></td><td style="text-align: right;;">5%</td><td style="text-align: right;;"></td><td style=";">Fruits</td><td style=";">Min</td><td style=";">Max</td><td style=";">DV</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Apple</td><td style="text-align: right;;">0.20%</td><td style="text-align: right;;">1.50%</td><td style="text-align: right;;">0.05</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Banana</td><td style="text-align: right;;">1.50%</td><td style="text-align: right;;">3.00%</td><td style="text-align: right;;">0.2</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Pear</td><td style="text-align: right;;">1.00%</td><td style="text-align: right;;">4.00%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Cherry</td><td style="text-align: right;;">4.00%</td><td style="text-align: right;;">10.00%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Peach</td><td style="text-align: right;;">5.00%</td><td style="text-align: right;;">20.00%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;">Min </td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Max</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;">5%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">20%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="color: #FF0000;;">Cell link = $C$1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A$1,$E$2:$G$6,ROWS(<font color="Red">$H$1:H2</font>),0</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A$1,$E$2:$G$6,ROWS(<font color="Red">$H$1:H3</font>),0</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
512
Hi everyone,

I found the following vba code which solved the problem :

Code:
Private Sub ScrollBar1_Change()
ActiveSheet.ScrollBar1.Max = Range("$H2").Value
ActiveSheet.ScrollBar1.Min = Range("H3").Value
End Sub

Thank you!
 

Forum statistics

Threads
1,144,663
Messages
5,725,658
Members
422,635
Latest member
crisis

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top