Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to create a ComboBox of ActiveX control using VBA

Status
Not open for further replies.

feipezi

IS-IT--Management
Aug 10, 2006
316
US
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.
 
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
 
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.
 



Hi,

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

Skip,

[glasses] [red][/red]
[tongue]
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top