Link Scroll Bar to cells

[mikebrooman]

Afternoon,

I've been using this forum for ages, and so far it's answered every one of my questions just by searching.
But now I'm stumped.
This is due to my lack of VBA knowladge!

I'm trying to link a scroll bar to a max, min, and increments.
It is a stardards scrollbar (design mode > scroll bar).

The Max cell is C7,
Min cell is D7.
Increment cell is E7.

I know it's the lazy option, but I'm so bad at VBA I'm really hoping to be able to copy a code straight in.

Oh and one more little bit, these valuse (max min etc) will change depending on what item I select (they are linked themselfs), so I think I will need a work sheet change event (I read this, I have NO idea what it is!)

Thanks for your time.

Mike

 

[Sektor]

Changing formula result doesn't raise Worksheet_Change event. This means your variables won't be updated...

 

[Weaver]

try:

Private Sub ScrollBar1_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As MSForms.fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    With ScrollBar1
        .Max = [C7]
        .Min = [D7]
        .SmallChange = [E7]
        .LargeChange = [E7]
    End With
End Sub

 

[mikebrooman]

Thanks weaver,
The increment worked, but it still has a max of 32767 (not what is in c7) and a min of 0 (not in D7).
any clues?

try:

Private Sub ScrollBar1_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As MSForms.fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
    With ScrollBar1
        .Max = [C7]
        .Min = [D7]
        .SmallChange = [E7]
        .LargeChange = [E7]
    End With
End Sub

 

[Weaver]

I'm not sure, it seemed to work when I was messing about with it, but I've deleted the sheet now. Maybe you could have a go at experimenting with some of the other events available.

 

[mikebrooman]

Thanks Weaver,
Maybe I'm misunderstanding where to put this code.
I right click on the scroll bar and then click view code, delete all the old code and past in the code you supplied. Then save and close VBA.
Is this right (I know it isn't how it mentions to do it in the link in your signiture, but this is for scroll bar not other macro)?

Thanks

 

[Weaver]

No that's right.

This all comes down to how we can trigger the change. I've had another look at this and think you might be able to do it under the 'scroll' event.

Private Sub ScrollBar1_Scroll()

The rest of the code stays the same.
You could probably update the .linkedcell property as well if you needed.

Of course, that's assuming your scrollbar is named 'ScrollBar1' ...

 

[mikebrooman]

Nice One

works a treat!
I will learn VBA one day, probably just in time to retire!

Thanks

 

[Weaver]

Nice One

works a treat!
I will learn VBA one day, probably just in time to retire!

Thanks

The reason I didn't go with this first was because I wasn't sure the parameters would update while the slider was active. Also, I'm not sure what happens say if you were to change the max value to a number that was less than what's already on the slider and vice versa with the min value.

I'd be interested to hear how you get on in this area.