Adding controls to a userform dynamically

[Shiles]

I'm having a real issue trying to hunt down a method by which i can add controls to a userform using code.

I'm trying to build multipul combo boxes, the number of which and values within being dependent on the content of a sheet.

The very basics of adding a combo box on initilisation of a form and defining it's properties within would be much appreciated.

Many Thanks

 

[Andrew Poulsom]

Why don't you add the Controls at design time and hide the ones you don't need?

If you still want to do it at run time, here's an example for OptionButtons:

http://spreadsheetpage.com/index.php/tip/creating_a_userform_programmatically/

 

[mikerickson]

This code is attached to a command button rather than to the Intialize event.
It sets a few of the new comboboxes properties. Note that the newComboBox variable is declared module wide.

How are you planning on attaching event code to the combo box.
Is the number of added comboboxes the same every run?
If a variable number, what is the maximum number of combo boxes that might be added.
Do all the added comboboxes use the same event code?

I completely argee with Andrew that controls should not be added at run time if not absolutely necessary.
And one should take great measures to make it unnecessary.

Dim newComboBox As MSForms.ComboBox

Private Sub CommandButton1_Click()
    Set newComboBox = UserForm1.Controls.Add("forms.ComboBox.1", Name:="myRunTimeComboBox", Visible:=True)
    With newComboBox
        .Top = 10: .Left = 10
        .Height = 23: .Width = 100
        .BackColor = RGB(255, 200, 200)
        With .Font
            .Bold = True
            .Name = "Arial"
            .Size = 12
        End With
        .AddItem "alpha"
        .AddItem "bravo"
        .AddItem "charlie"
    End With
End Sub

 

[Shiles]

Thank you both for your help. The code you've provided works like a charm.

I'm in the process of redesigning a tool i've built which allow it to be redesigned based on entries in a spread sheet.

The combo boxes will only be used to add data to a sheet but the number of them is undefined as are their properties, the names later being link check boxes in a treeview which applies a criteria filter to data.

Thanks again for you help!

 

[Norie]

Just out of curiosity what data will each combobox have?

Is each one a list of values in a field/column on the worksheet?

Sort of like you have with filters.