add worksheet checkbox and change parameters

R

robert.hatcher

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
 
D

Dave Peterson

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
 
R

robert.hatcher

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
 
D

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.
 
R

robert.hatcher

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 :(
 
D

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!)

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 :(
Click to expand...
 
R

robert.hatcher

Dave said:
If I use:

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

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
 
D

Dave Peterson

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.
 
R

robert.hatcher

Excellent idea


Dave said:
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.
Click to expand...
 
R

robert.hatcher

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
 
D

Dave Peterson

Don't start stepping through the code until you're past the .add statement.

Or run through that line--stepping through code that adds a control from the
control toolbox toolbar has always done the same thing for me, too.

I've learned to avoid that line <bg>.

(Just put a break point after that line.)
 
R

robert.hatcher

Using the following code and putting a breakpoint at:
Range("a1").Activate
and selecting "Run" I still get "Cant enter break mode at this time"
and the same results :(

Public Sub InsertChkBx1()
Dim OLEObj As OLEObject
Dim chkBxNm As String
Dim chkBxCap As String

chkBxNm = "NameA"
chkBxCap = "CaptionA"

Range("a1").Activate

'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.Name = chkBxNm
OLEObj.Object.Caption = chkBxCap
OLEObj.Object.Font.Size = 6
OLEObj.Object.MousePointer = 14
End With

Range("a1").Activate ' BREAKPOINT set here
 
D

Dave Peterson

Same with me (this time).

I think(!) I've had success with what I mentioned earlier--but I can't prove it
now.

Sorry.

Using the following code and putting a breakpoint at:
Range("a1").Activate
and selecting "Run" I still get "Cant enter break mode at this time"
and the same results :(

Public Sub InsertChkBx1()
Dim OLEObj As OLEObject
Dim chkBxNm As String
Dim chkBxCap As String

chkBxNm = "NameA"
chkBxCap = "CaptionA"

Range("a1").Activate

'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.Name = chkBxNm
OLEObj.Object.Caption = chkBxCap
OLEObj.Object.Font.Size = 6
OLEObj.Object.MousePointer = 14
End With

Range("a1").Activate ' BREAKPOINT set here
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem Populating a ComboBox 3
checkbox linked to a cell 1
Prgrammatically added OLE Listbox, now cannot select from it 9
trying to put a check box in each shell with vba 3
programming checkbox properties 5
Command Buttons Location 2
Programmatically add control to worksheet. 3
Excel crashes and restarts when accessing newly created worksheet 2

Top