Change UserForm dynamically based on input

[Dmitry_D_Onishchenko]

Hi MrExcel, I'll greatly appreciate your input re the issue below.

I'm looking for an option to add elements (such as ListBoxes) to a Userform in a dynamic/interactive way: the number of ListBoxes (as well as their names, tab indices etc.) should depend on a specific input value (say, on the number of selected items in a ListBox or on the item selected in a ComboBox). Thus, two questions arise.

1) In VBA, is there a way to dynamically add elements (such as ListBoxes) to the UserForm once the UserForm is initialized?
2) If no, what could be the best alternative option?

In case some context is needed:
I'm working on a solution to simultaneously filter multiple worksheets based on multiple criteria. A user may select from a ListBox an arbitrary number of fields to be used as filters. Then I want the corresponding number of ListBoxes to appear with the filter values themselves. Obviously, the number of ListBoxes needed is not known at the time when UserForm is initialized.

Thanks in advance,
Dmitry

 

[lrobbo314]

You can add controls via VBA like this.

Private Sub UserForm_Initialize()
With Me.Controls.Add("Forms.Listbox.1", "lst1")
    .Top = 10
    .Left = 10
End With
End Sub

 

[Dmitry_D_Onishchenko]

You can add controls via VBA like this.

Yes I know, but as I said in the first post, the key issue here is to add controls after the form is initialized and based on the input in one of the controls (say, a ListBox). I can't figure out how to implement this.

 

[Dmitry_D_Onishchenko]

Ok, I have finally figured out how to update the UserForm dynamically based on a ListBox selection (or any other control selection). This can be done inside the _Change() sub:

Private Sub ListBox1_Change()
' 1) save the items selected in ListBox1 to an array SelectedItems()
' ...
' 2) add controls looping through the selected items
    Dim I As Long, lstbox As MSForms.ListBox
    For I = 1 To ArrayLen(SelectedItems) ' ArrayLen() is a user-defined function to calculate the array length
        With Me.Controls
            Set lstbox = .Add("Forms.ListBox.1", Name:="lstbox_" & SelectedItems(I - 1), Visible:=True)
            With lstbox
                ' set position properties here
            End With
        End With
    Next I
End Sub

Problem solved.