Form Controls & Sliders

[dannyok90]

Hi All,

I'm just wondering if anybody has ever used two sliders to control one cells value?


I know you can link both sliders to the value cell and they will mimic each other but
has anybody used VBA, so when you move one slider the value snaps to that value and when you use
the other one again it snaps back to that one.


for context:

I have 6 rows that use sliders to control percentage. I also have two other global sliders that control
three rows each. I'd like to be able to use each of the six rows sliders independently but when the
global sliders are used the value snaps to that setting, and when the user goes back to the individual slider it snaps back to its old value in relation to the sliders position.


Thanks in advance!

Kind Regards,
Dan

 

[AlphaFrog]

• Use Form-type sliders (ScrollBars) and not ActiveX-type
• Don't link a cell to any of the sliders
• Assign the row (local) sliders to the Slider_Local macro
• Assign the global slider to the Slider_Global macro

In this example, the percentage values are in B2:B4 and the sliders are in C2:C4. The Slider_Local macro changes the cell to the left of the slider that is adjusted. The Slider_Global macro changes all three cells in range B2:B4 when Scroll Bar 4 is adjusted. You will of course have to change the code to suit your layout.

[COLOR=darkblue]Sub[/COLOR] Slider_Local()
    [COLOR=darkblue]With[/COLOR] ActiveSheet.ScrollBars(Application.Caller)
        .TopLeftCell.Offset(0, -1).Value = .Value / 100
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]


[COLOR=darkblue]Sub[/COLOR] Slider_Global()
    [COLOR=darkblue]With[/COLOR] ActiveSheet.ScrollBars("Scroll Bar 4")
        Range("B2:B4").Value = .Value / 100
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
[COLOR=darkblue]End[/COLOR] Sub

 

[dannyok90]

Hi Alpha Frog,

Thanks for your reply! ill have a go with this tomorrow and come back to you!

Thank,
Dan