I'm trying to add a control to a worksheet and then modify its
parameters. To learn how to do this I have the following code:

Option Explicit
Public Sub InsertChkBx1()
'insert checkbox at cell B2
ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
End Sub


Public Sub renameChkBx()
Sheet1.CheckBox1.Caption = "testbx"
End Sub

Running Sub InsertChkBx1 and then renameChkBx works fine.

The next step is run them from one piece of code...

Public Sub InsertChkBx1()

'insert checkbox at cell B2
ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5

Sheet1.CheckBox1.Caption = "testbx"

End Sub

But I get a compile error highlighting "checkBox1" - Method or
data member not found. Probably because there is no checkbox yet. I
decided to keep them separate and just call the second procedure from
the first...

Public Sub InsertChkBx1()

'insert checkbox at cell B2
ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5

renameChkBx

End Sub

Public Sub renameChkBx()

Sheet1.CheckBox1.Caption = "testbx"

End Sub

But I get the same error just down in the second procedure.

Any help will be greatly appreciated
Robert

This worked ok for me:

Option Explicit
Public Sub InsertChkBx1()
Dim OLEObj As OLEObject

'insert checkbox at cell B2
With ActiveSheet
Set OLEObj = .OLEObjects.Add(classtype:="Forms.CheckBox.1", _
Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5)
OLEObj.Object.Caption = "testbx"
End With

End Sub

(E-Mail Removed) wrote:
>
> I'm trying to add a control to a worksheet and then modify its
> parameters. To learn how to do this I have the following code:
>
> Option Explicit
> Public Sub InsertChkBx1()
> 'insert checkbox at cell B2
> ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
> End Sub
>
> Public Sub renameChkBx()
> Sheet1.CheckBox1.Caption = "testbx"
> End Sub
>
> Running Sub InsertChkBx1 and then renameChkBx works fine.
>
> The next step is run them from one piece of code...
>
> Public Sub InsertChkBx1()
>
> 'insert checkbox at cell B2
> ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
>
> Sheet1.CheckBox1.Caption = "testbx"
>
> End Sub
>
> But I get a compile error highlighting "checkBox1" - Method or
> data member not found. Probably because there is no checkbox yet. I
> decided to keep them separate and just call the second procedure from
> the first...
>
> Public Sub InsertChkBx1()
>
> 'insert checkbox at cell B2
> ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
>
> renameChkBx
>
> End Sub
>
> Public Sub renameChkBx()
>
> Sheet1.CheckBox1.Caption = "testbx"
>
> End Sub
>
> But I get the same error just down in the second procedure.
>
> Any help will be greatly appreciated
> Robert

--

Dave Peterson

Thanks dave, that worked fine. However, trying to build on that I got
stumped right away
to change the name I tried adding :

OLEObj.Object.Name = "testbx"

right after

OLEObj.Object.Caption = "testbx"

and I get a runtime error 438 "Object doesn't support this property or
method"

I tried a few other items that I thought were valid properties but got
similar results. it seems like there are "catagories" of properties
for controls and the help is isnt doing my in clearifying that...

Can I change the name and other properties using the method you
demonstriated or is that limited to the Caption?

Is there a good reference on this topic?

thanks
Robert

Dave Peterson wrote:
> This worked ok for me:
>
> Option Explicit
> Public Sub InsertChkBx1()
> Dim OLEObj As OLEObject
>
> 'insert checkbox at cell B2
> With ActiveSheet
> Set OLEObj = .OLEObjects.Add(classtype:="Forms.CheckBox.1", _
> Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5)
> OLEObj.Object.Caption = "testbx"
> End With
>
> End Sub
>
> (E-Mail Removed) wrote:
> >
> > I'm trying to add a control to a worksheet and then modify its
> > parameters. To learn how to do this I have the following code:
> >
> > Option Explicit
> > Public Sub InsertChkBx1()
> > 'insert checkbox at cell B2
> > ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
> > End Sub
> >
> > Public Sub renameChkBx()
> > Sheet1.CheckBox1.Caption = "testbx"
> > End Sub
> >
> > Running Sub InsertChkBx1 and then renameChkBx works fine.
> >
> > The next step is run them from one piece of code...
> >
> > Public Sub InsertChkBx1()
> >
> > 'insert checkbox at cell B2
> > ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
> >
> > Sheet1.CheckBox1.Caption = "testbx"
> >
> > End Sub
> >
> > But I get a compile error highlighting "checkBox1" - Method or
> > data member not found. Probably because there is no checkbox yet. I
> > decided to keep them separate and just call the second procedure from
> > the first...
> >
> > Public Sub InsertChkBx1()
> >
> > 'insert checkbox at cell B2
> > ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
> >
> > renameChkBx
> >
> > End Sub
> >
> > Public Sub renameChkBx()
> >
> > Sheet1.CheckBox1.Caption = "testbx"
> >
> > End Sub
> >
> > But I get the same error just down in the second procedure.
> >
> > Any help will be greatly appreciated
> > Robert
>
> --
>
> Dave Peterson

Try
oleobj.name = "testbx"

And the object browser may help--or a book???

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's is a nice one to start with.

See if you can find them in your local bookstore/internet site and you can
choose what one you like best.

(E-Mail Removed) wrote:
>
> Thanks dave, that worked fine. However, trying to build on that I got
> stumped right away
> to change the name I tried adding :
>
> OLEObj.Object.Name = "testbx"
>
> right after
>
> OLEObj.Object.Caption = "testbx"
>
> and I get a runtime error 438 "Object doesn't support this property or
> method"
>
> I tried a few other items that I thought were valid properties but got
> similar results. it seems like there are "catagories" of properties
> for controls and the help is isnt doing my in clearifying that...
>
> Can I change the name and other properties using the method you
> demonstriated or is that limited to the Caption?
>
> Is there a good reference on this topic?
>
> thanks
> Robert
>
> Dave Peterson wrote:
> > This worked ok for me:
> >
> > Option Explicit
> > Public Sub InsertChkBx1()
> > Dim OLEObj As OLEObject
> >
> > 'insert checkbox at cell B2
> > With ActiveSheet
> > Set OLEObj = .OLEObjects.Add(classtype:="Forms.CheckBox.1", _
> > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5)
> > OLEObj.Object.Caption = "testbx"
> > End With
> >
> > End Sub
> >
> > (E-Mail Removed) wrote:
> > >
> > > I'm trying to add a control to a worksheet and then modify its
> > > parameters. To learn how to do this I have the following code:
> > >
> > > Option Explicit
> > > Public Sub InsertChkBx1()
> > > 'insert checkbox at cell B2
> > > ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> > > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
> > > End Sub
> > >
> > > Public Sub renameChkBx()
> > > Sheet1.CheckBox1.Caption = "testbx"
> > > End Sub
> > >
> > > Running Sub InsertChkBx1 and then renameChkBx works fine.
> > >
> > > The next step is run them from one piece of code...
> > >
> > > Public Sub InsertChkBx1()
> > >
> > > 'insert checkbox at cell B2
> > > ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> > > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
> > >
> > > Sheet1.CheckBox1.Caption = "testbx"
> > >
> > > End Sub
> > >
> > > But I get a compile error highlighting "checkBox1" - Method or
> > > data member not found. Probably because there is no checkbox yet. I
> > > decided to keep them separate and just call the second procedure from
> > > the first...
> > >
> > > Public Sub InsertChkBx1()
> > >
> > > 'insert checkbox at cell B2
> > > ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> > > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
> > >
> > > renameChkBx
> > >
> > > End Sub
> > >
> > > Public Sub renameChkBx()
> > >
> > > Sheet1.CheckBox1.Caption = "testbx"
> > >
> > > End Sub
> > >
> > > But I get the same error just down in the second procedure.
> > >
> > > Any help will be greatly appreciated
> > > Robert
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

As usual, your sugestion worked!

when I type OLEObj.(OLEObject) the auto list feature prompts the
methods and properties that are available. The object browser and help
provide all the subordinate methods and properties as well. However,
auto list does nothing with OLEObj.Object. and The object browser and
help as dead ends as well. Asside from your experience, how do you
know what methods and properties work with "Object" in this usage?

Object Property Help says:
Returns the OLE Automation object associated with this OLE object.
Read-only Object.

not meaning to sound dense but that statement makes my head hurt

Dave Peterson wrote:
> Try
> oleobj.name = "testbx"
>
> And the object browser may help--or a book???
>
> Debra Dalgleish has a list of books at her site:
> http://www.contextures.com/xlbooks.html
>
> John Walkenbach's is a nice one to start with.
>
> See if you can find them in your local bookstore/internet site and you can
> choose what one you like best.
>
> (E-Mail Removed) wrote:
> >
> > Thanks dave, that worked fine. However, trying to build on that I got
> > stumped right away
> > to change the name I tried adding :
> >
> > OLEObj.Object.Name = "testbx"
> >
> > right after
> >
> > OLEObj.Object.Caption = "testbx"
> >
> > and I get a runtime error 438 "Object doesn't support this property or
> > method"
> >
> > I tried a few other items that I thought were valid properties but got
> > similar results. it seems like there are "catagories" of properties
> > for controls and the help is isnt doing my in clearifying that...
> >
> > Can I change the name and other properties using the method you
> > demonstriated or is that limited to the Caption?
> >
> > Is there a good reference on this topic?
> >
> > thanks
> > Robert
> >
> > Dave Peterson wrote:
> > > This worked ok for me:
> > >
> > > Option Explicit
> > > Public Sub InsertChkBx1()
> > > Dim OLEObj As OLEObject
> > >
> > > 'insert checkbox at cell B2
> > > With ActiveSheet
> > > Set OLEObj = .OLEObjects.Add(classtype:="Forms.CheckBox.1", _
> > > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5)
> > > OLEObj.Object.Caption = "testbx"
> > > End With
> > >
> > > End Sub
> > >
> > > (E-Mail Removed) wrote:
> > > >
> > > > I'm trying to add a control to a worksheet and then modify its
> > > > parameters. To learn how to do this I have the following code:
> > > >
> > > > Option Explicit
> > > > Public Sub InsertChkBx1()
> > > > 'insert checkbox at cell B2
> > > > ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> > > > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
> > > > End Sub
> > > >
> > > > Public Sub renameChkBx()
> > > > Sheet1.CheckBox1.Caption = "testbx"
> > > > End Sub
> > > >
> > > > Running Sub InsertChkBx1 and then renameChkBx works fine.
> > > >
> > > > The next step is run them from one piece of code...
> > > >
> > > > Public Sub InsertChkBx1()
> > > >
> > > > 'insert checkbox at cell B2
> > > > ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> > > > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
> > > >
> > > > Sheet1.CheckBox1.Caption = "testbx"
> > > >
> > > > End Sub
> > > >
> > > > But I get a compile error highlighting "checkBox1" - Method or
> > > > data member not found. Probably because there is no checkbox yet. I
> > > > decided to keep them separate and just call the second procedure from
> > > > the first...
> > > >
> > > > Public Sub InsertChkBx1()
> > > >
> > > > 'insert checkbox at cell B2
> > > > ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> > > > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
> > > >
> > > > renameChkBx
> > > >
> > > > End Sub
> > > >
> > > > Public Sub renameChkBx()
> > > >
> > > > Sheet1.CheckBox1.Caption = "testbx"
> > > >
> > > > End Sub
> > > >
> > > > But I get the same error just down in the second procedure.
> > > >
> > > > Any help will be greatly appreciated
> > > > Robert
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

A lot of it comes from experience--a lot of trial and error (for me anyway!).

If I use:

oleobj.
and don't see what I want in the intellisense, I try:
oleobj.object
and see if that works.

(Remember to save often--I never know what I'm gonna screw up next!)

(E-Mail Removed) wrote:
>
> As usual, your sugestion worked!
>
> when I type OLEObj.(OLEObject) the auto list feature prompts the
> methods and properties that are available. The object browser and help
> provide all the subordinate methods and properties as well. However,
> auto list does nothing with OLEObj.Object. and The object browser and
> help as dead ends as well. Asside from your experience, how do you
> know what methods and properties work with "Object" in this usage?
>
> Object Property Help says:
> Returns the OLE Automation object associated with this OLE object.
> Read-only Object.
>
> not meaning to sound dense but that statement makes my head hurt
>
> Dave Peterson wrote:
> > Try
> > oleobj.name = "testbx"
> >
> > And the object browser may help--or a book???
> >
> > Debra Dalgleish has a list of books at her site:
> > http://www.contextures.com/xlbooks.html
> >
> > John Walkenbach's is a nice one to start with.
> >
> > See if you can find them in your local bookstore/internet site and you can
> > choose what one you like best.
> >
> > (E-Mail Removed) wrote:
> > >
> > > Thanks dave, that worked fine. However, trying to build on that I got
> > > stumped right away
> > > to change the name I tried adding :
> > >
> > > OLEObj.Object.Name = "testbx"
> > >
> > > right after
> > >
> > > OLEObj.Object.Caption = "testbx"
> > >
> > > and I get a runtime error 438 "Object doesn't support this property or
> > > method"
> > >
> > > I tried a few other items that I thought were valid properties but got
> > > similar results. it seems like there are "catagories" of properties
> > > for controls and the help is isnt doing my in clearifying that...
> > >
> > > Can I change the name and other properties using the method you
> > > demonstriated or is that limited to the Caption?
> > >
> > > Is there a good reference on this topic?
> > >
> > > thanks
> > > Robert
> > >
> > > Dave Peterson wrote:
> > > > This worked ok for me:
> > > >
> > > > Option Explicit
> > > > Public Sub InsertChkBx1()
> > > > Dim OLEObj As OLEObject
> > > >
> > > > 'insert checkbox at cell B2
> > > > With ActiveSheet
> > > > Set OLEObj = .OLEObjects.Add(classtype:="Forms.CheckBox.1", _
> > > > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5)
> > > > OLEObj.Object.Caption = "testbx"
> > > > End With
> > > >
> > > > End Sub
> > > >
> > > > (E-Mail Removed) wrote:
> > > > >
> > > > > I'm trying to add a control to a worksheet and then modify its
> > > > > parameters. To learn how to do this I have the following code:
> > > > >
> > > > > Option Explicit
> > > > > Public Sub InsertChkBx1()
> > > > > 'insert checkbox at cell B2
> > > > > ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> > > > > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
> > > > > End Sub
> > > > >
> > > > > Public Sub renameChkBx()
> > > > > Sheet1.CheckBox1.Caption = "testbx"
> > > > > End Sub
> > > > >
> > > > > Running Sub InsertChkBx1 and then renameChkBx works fine.
> > > > >
> > > > > The next step is run them from one piece of code...
> > > > >
> > > > > Public Sub InsertChkBx1()
> > > > >
> > > > > 'insert checkbox at cell B2
> > > > > ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> > > > > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
> > > > >
> > > > > Sheet1.CheckBox1.Caption = "testbx"
> > > > >
> > > > > End Sub
> > > > >
> > > > > But I get a compile error highlighting "checkBox1" - Method or
> > > > > data member not found. Probably because there is no checkbox yet. I
> > > > > decided to keep them separate and just call the second procedure from
> > > > > the first...
> > > > >
> > > > > Public Sub InsertChkBx1()
> > > > >
> > > > > 'insert checkbox at cell B2
> > > > > ActiveSheet.OLEObjects.Add classtype:="Forms.CheckBox.1", _
> > > > > Left:=48.75, Top:=13.5, Height:=11.25, Width:=46.5
> > > > >
> > > > > renameChkBx
> > > > >
> > > > > End Sub
> > > > >
> > > > > Public Sub renameChkBx()
> > > > >
> > > > > Sheet1.CheckBox1.Caption = "testbx"
> > > > >
> > > > > End Sub
> > > > >
> > > > > But I get the same error just down in the second procedure.
> > > > >
> > > > > Any help will be greatly appreciated
> > > > > Robert
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Dave Peterson wrote:

> If I use:
>
> oleobj.
> and don't see what I want in the intellisense, I try:
> oleobj.object
> and see if that works.

Too Funny, thats exactly how I figured out how to access the font
property. Which is actualy part of MSForms but you dont dont access
it with ...MSForms.Checkbox.font etc. but use OLEObject.Object.Font...
Its like "Object" is a placeholder for what ever is being borrowed from
the MSforms library. And if Im not right, I dont care

perpetualy learning
Robert

When you're learning (or just struggling), you can add a watch to the OLEObj
variable.

Then expand the properties for that variable in the watch window. You can drill
down as far as you need while you're searching for the property that you want.



(E-Mail Removed) wrote:
>
> Dave Peterson wrote:
>
> > If I use:
> >
> > oleobj.
> > and don't see what I want in the intellisense, I try:
> > oleobj.object
> > and see if that works.
>
> Too Funny, thats exactly how I figured out how to access the font
> property. Which is actualy part of MSForms but you dont dont access
> it with ...MSForms.Checkbox.font etc. but use OLEObject.Object.Font...
> Its like "Object" is a placeholder for what ever is being borrowed from
> the MSforms library. And if Im not right, I dont care
>
> perpetualy learning
> Robert

--

Dave Peterson

Excellent idea


Dave Peterson wrote:
> When you're learning (or just struggling), you can add a watch to the OLEObj
> variable.
>
> Then expand the properties for that variable in the watch window. You can drill
> down as far as you need while you're searching for the property that you want.
>
>
>
> (E-Mail Removed) wrote:
> >
> > Dave Peterson wrote:
> >
> > > If I use:
> > >
> > > oleobj.
> > > and don't see what I want in the intellisense, I try:
> > > oleobj.object
> > > and see if that works.
> >
> > Too Funny, thats exactly how I figured out how to access the font
> > property. Which is actualy part of MSForms but you dont dont access
> > it with ...MSForms.Checkbox.font etc. but use OLEObject.Object.Font...
> > Its like "Object" is a placeholder for what ever is being borrowed from
> > the MSforms library. And if Im not right, I dont care
> >
> > perpetualy learning
> > Robert
>
> --
>
> Dave Peterson

Actualy when I try to do that as soon as I step down to the Set line, I
get an error "Cant enter break mode at this time" if I select
"continue" the code runs to the end and I dont get to look at the
OLEObj.

Robert


(E-Mail Removed) wrote:
> Excellent idea
>
>
> Dave Peterson wrote:
> > When you're learning (or just struggling), you can add a watch to the OLEObj
> > variable.
> >
> > Then expand the properties for that variable in the watch window. You can drill
> > down as far as you need while you're searching for the property that you want.
> >
> >
> >
> > (E-Mail Removed) wrote:
> > >
> > > Dave Peterson wrote:
> > >
> > > > If I use:
> > > >
> > > > oleobj.
> > > > and don't see what I want in the intellisense, I try:
> > > > oleobj.object
> > > > and see if that works.
> > >
> > > Too Funny, thats exactly how I figured out how to access the font
> > > property. Which is actualy part of MSForms but you dont dont access
> > > it with ...MSForms.Checkbox.font etc. but use OLEObject.Object.Font...
> > > Its like "Object" is a placeholder for what ever is being borrowed from
> > > the MSforms library. And if Im not right, I dont care
> > >
> > > perpetualy learning
> > > Robert
> >
> > --
> >
> > Dave Peterson