Adding listbox to form created in Excel VBA

[ejsmith]

I am trying to create a form which contains several listboxes in excel using VBA. I want the form and listboxes created via code so that the code can be transfered easily to many users.
I know how to create a form and I know how to create a listbox - problem is I don't know how to create a listbox ON a form...
Here is what I have so far - which creates the form and listbox seperately:

ActiveSheet.OLEObjects.Add "Forms.Form.1", _
Left:=150, Top:=25, Height:=100, Width:=100

ActiveSheet.OLEObjects.Add "Forms.ComboBox.1", _
Left:=250, Top:=50, Height:=20, Width:=100


Any help would be greatly appreciated!

 

[RobSchultz]

Any reason you don't want to use the UserForm editor?

Rob

-Focus on the solution to the problem, not the obstacles in the way.-

 

[ejsmith]

Well, what I'm writing may be distributed to many people - I want to be able to just email them the code without having to deal with getting them the forms too...
I thought the only way I could do that would be to crete the form and comboboxes dynamically.

 

[RobSchultz]

You aren't just e-mailing them the .xls file? If you are then the form would go along for the ride.

If you do need to create the list box dynamically then use the macro recorder and use what it finds.

Regards,

Rob

-Focus on the solution to the problem, not the obstacles in the way.-

 

[ejsmith]

Thanks Rob!