what object var should I use for the following code

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

TONY - 09 Dec 2007 02:14 GMT

I recorded the following code for adding a scrollbar to a sheet.... no
problem. So I cut and paste the code into my module and it runs fine.
However what I can not figure out is what OBJECT varriable I can use with
this statement so I can use the object varriable in the WITH END WITH
cinstruct so I can reach and change different properties of the control.

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ScrollBar.1", Link:=False, _
DisplayAsIcon:=False, Left:=cw * 5, Top:=7, Width:=15#, Height:=289 _
).Select

Reply to this Message

Dave Peterson - 09 Dec 2007 02:47 GMT

dim OLEObj as OLEObject
set oleobj = activesheet.oleobjects.add(...)
with oleobj
....

Depending on what you're doing, you may want:

With OLEObj.object
....

> I recorded the following code for adding a scrollbar to a sheet.... no
> problem. So I cut and paste the code into my module and it runs fine.
[quoted text clipped - 5 lines]
> DisplayAsIcon:=False, Left:=cw * 5, Top:=7, Width:=15#, Height:=289 _
> ).Select

Signature

Dave Peterson

Reply to this Message

Jim Cone - 09 Dec 2007 02:49 GMT

Sub HowToDoIt()
Dim objSB As OLEObject
Dim cw As Long
cw = 20
Set objSB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ScrollBar.1", Link:=False, _
DisplayAsIcon:=False, Left:=cw * 5, Top:=7, Width:=15#, Height:=289)
objSB.Name = "Mush"
Set objSB = Nothing
End Sub

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"TONY"
wrote in message
I recorded the following code for adding a scrollbar to a sheet.... no
problem. So I cut and paste the code into my module and it runs fine.
However what I can not figure out is what OBJECT varriable I can use with
this statement so I can use the object varriable in the WITH END WITH
cinstruct so I can reach and change different properties of the control.

ActiveSheet.OLEObjects.Add(ClassType:="Forms.ScrollBar.1", Link:=False, _
DisplayAsIcon:=False, Left:=cw * 5, Top:=7, Width:=15#, Height:=289 _
).Select

Reply to this Message

TONY - 09 Dec 2007 03:13 GMT

When I try this I get the following Error message.

"Cant enter break mode at this time"

> Sub HowToDoIt()
> Dim objSB As OLEObject
[quoted text clipped - 5 lines]
> Set objSB = Nothing
> End Sub

Reply to this Message

Jim Cone - 09 Dec 2007 03:23 GMT

You are stepping thru the code (F8), instead just let it run (F5).
Also, as Dave said, you many need to use objSB.Object to set some of the properties.
Jim Cone

"TONY"
wrote in message
When I try this I get the following Error message.
"Cant enter break mode at this time"

"Jim Cone" wrote:

> Sub HowToDoIt()
> Dim objSB As OLEObject
[quoted text clipped - 5 lines]
> Set objSB = Nothing
> End Sub

> "TONY"
> wrote in message
[quoted text clipped - 7 lines]
> DisplayAsIcon:=False, Left:=cw * 5, Top:=7, Width:=15#, Height:=289 _
> ).Select

Reply to this Message