How to create a ComboBox of ActiveX control using VBA

[feipezi]

Hi,
Actually, I have already created a ComboBox of ActiveX control using the following code:

Sub CrtComboBoxes_ActiveX()
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=69, Top:=32.25, Width:=72, Height:=16).Select
End Sub

My problem is that I have to manually key in some properties like 'LinkedCell', 'ListFillRange' and more.
I am wondering if I can set up the properties in the code above. I tried but I got errors like 'Run-time error: 438': Named argument not found. I looks like it won't allow me to insert any other parameters beside those in the parentheses.

I did ComboBox of Form using the object of ControlFormat and it worked but that's not what I want. I want ActiveX.

Thanks in advance.

 

[PHV]

You may try something like this:
Sub CrtComboBoxes_ActiveX()
Dim myCB As Object
Set myCB = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=69, Top:=32.25, Width:=72, Height:=16)
myCB.LinkedCell = ...
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[feipezi]

Thanks PHV, it really works.
One more question. When I do
...
...
myCB.Text = "Something" or
myCB.Value = "Something"
...
...

then I will end up with some error like this type of use not supported, things like that.

When I tried to set up a Label and I do

...
myLBL.Cation = "Something"
...

I will get the same error.
Any suggestions for me to get around the pitfall?
Thanks in advance.

 

[SkipVought]

Hi,

Check out How to use the Watch Window as a Power Programming Tool faq707-4594.

Skip,

_[red][/red]

 

[feipezi]

Thanks folks! Please ignore my last questions. I think I found the answers.
If I do

...
...
myCB.Object.Text (or Value) = "whatever"
...
myLBL.Object.Caption = "Something"
...
...

It looks like myCB is not Text's parent.
Thanks again.
feipezi