Sleeper: Insert Scroll bar from code

[Maka]

hi, I have tried this thread in some formus but still no answer. I am tryin to insert a scrollbar from my code with specific properties but some windows alerts appear an other warnings dont let me fix it. Thanks


Sub ScrollBar(MyRange As Range)
Dim MyObject As OLEObject
Set MyObject = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ScrollBar.1")
With MyObject
.Height = MyRange.Height
.Width = MyRange.Width
.LinkedCell = MyRange.Offset(, -1)
'Errors from here to end
.Max = 100
.Min = 0
.SmallChange = 1
.LargeChange = 5
End With
End Sub

[Greg T]

You have to use the OLEObject's .Object property (please do not ask me why -- I do not know.) This worked for me.


Sub AddScrollBar(MyRange As Range)
Dim MyObject As OLEObject
Set MyObject = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ScrollBar.1")
With MyObject
.Height = MyRange.Height
.Width = MyRange.Width * 3
.Top = MyRange.Top
.Left = MyRange.Left
.LinkedCell = MyRange.Offset(, -1).Address
With .Object
.Max = 100
.Min = 0
.SmallChange = 1
.LargeChange = 5
End With
End With
End Sub

[Maka]

thanks a lot, i have tried everything and posted it everywhere

[Ken Puls]

Hi Maka,

Just a quick question for you... Have you posted at the other sites to let them know the issue is solved? If not, please post a link at each site to the answer you received here.

When posting at multiple sites, we really appreciate it if you can link to the others. Members who travel across boards tend to find the thread that is active and post there, rather than have several people at different sites all working on independant approaches. I am not in any way saying don't post at multiple places, just please link to them when you do.:yes

Oh! And Welcome to VBAX! :hi:

Cheers!