Change UserForm dynamically based on input

Joined
Nov 27, 2019
Messages
18
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,155
Office Version
  1. 365
Platform
  1. Windows
You can add controls via VBA like this.

VBA Code:
Private Sub UserForm_Initialize()
With Me.Controls.Add("Forms.Listbox.1", "lst1")
    .Top = 10
    .Left = 10
End With
End Sub
 
Joined
Nov 27, 2019
Messages
18
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
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.
 
Joined
Nov 27, 2019
Messages
18
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
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:

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,381
Messages
5,641,827
Members
417,240
Latest member
pjohnsonexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top