what object var should I use for the following code
 

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.Scrol lBar.1", Link:=False, _
DisplayAsIcon:=False, Left:=cw * 5, Top:=7, Width:=15#, Height:=289 _
).Select

what object var should I use for the following code
 

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

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

With OLEObj.object
....



TONY wrote:

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.Scrol lBar.1", Link:=False, _
DisplayAsIcon:=False, Left:=cw * 5, Top:=7, Width:=15#, Height:=289 _
).Select

--

Dave Peterson

what object var should I use for the following code
 

Sub HowToDoIt()
Dim objSB As OLEObject
Dim cw As Long
cw = 20
Set objSB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Scrol lBar.1", Link:=False, _
DisplayAsIcon:=False, Left:=cw * 5, Top:=7, Width:=15#, Height:=289)
objSB.Name = "Mush"
Set objSB = Nothing
End Sub
--
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.Scrol lBar.1", Link:=False, _
DisplayAsIcon:=False, Left:=cw * 5, Top:=7, Width:=15#, Height:=289 _
).Select

what object var should I use for the following code
 

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
Dim cw As Long
cw = 20
Set objSB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Scrol lBar.1", Link:=False, _
DisplayAsIcon:=False, Left:=cw * 5, Top:=7, Width:=15#, Height:=289)
objSB.Name = "Mush"
Set objSB = Nothing
End Sub
--
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.Scrol lBar.1", Link:=False, _
DisplayAsIcon:=False, Left:=cw * 5, Top:=7, Width:=15#, Height:=289 _
).Select

what object var should I use for the following code
 

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
Dim cw As Long
cw = 20
Set objSB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Scrol lBar.1", Link:=False, _
DisplayAsIcon:=False, Left:=cw * 5, Top:=7, Width:=15#, Height:=289)
objSB.Name = "Mush"
Set objSB = Nothing
End Sub
--
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.Scrol lBar.1", Link:=False, _
DisplayAsIcon:=False, Left:=cw * 5, Top:=7, Width:=15#, Height:=289 _
).Select