Modify Scroll Bar Control Properties

[Pflugs]

I created a ScrollBar control and linked it to a cell. I also want the min
and max properties linked to a cell. I know this is not directly possible,
so I'm trying to write a Worksheet_Change event that updates the properties
when the specified cells change.

I can't figure out how to correctly set the scrollbar to an object that
gives me access to the Min and Max property. The control is of the type
MSForms.ScrollBar, but I can only figure out how to refer to it as a Shape or
a OLEObject, neither of which allow me to set the max or min properties.

Can anyone advise further? Thanks,
Pflugs

 

[Toyin.Butler]

I created a ScrollBar control and linked it to a cell. I also want the min
and max properties linked to a cell. I know this is not directly possible,
so I'm trying to write a Worksheet_Change event that updates the properties
when the specified cells change.

I can't figure out how to correctly set the scrollbar to an object that
gives me access to the Min and Max property. The control is of the type
MSForms.ScrollBar, but I can only figure out how to refer to it as a Shape or
a OLEObject, neither of which allow me to set the max or min properties.

Can anyone advise further? Thanks,
Pflugs

The only way that I have been able to this is to select the object and
then change the properties as follows. I'm sure there is a more
efficient way of completing this task. Maybe someone can shed some
light.

ActiveSheet.Shapes("Scroll Bar 1").Select
Selection.Min = 1
Selection.Max = 100

Regards,

Toyin.

 

[Pflugs]

That method doesn't work with ActiveX controls (from the Control Toolbox). I
am not sure if regular forms controls will work for my application.

Thanks,
Pflugs

 

[Toyin.Butler]

For an activeX control try the following:

Private Sub Worksheet_Change(ByVal Target As Range)
Me.ScrollBar1.Max = 1
Me.ScrollBar1.Max = 100
End Sub

nb. Remember to take switch off design mode! lol.

Regards,

Toyin.

 

[Pflugs]

Oh, good grief. I can't believe it was that easy. That's just like a
userform. I suppose it would be since a sheet is an object just like a
userform.

Thanks for your help!
Pflugs