Using the Unique formula multiple times on User form in Excel

userFWxZ 0
Viewed 29

I have created a user form which populates a table, I want this form to basically show the information already in the table as a list. So to do this, I have a Unique list taht the user form pulls from to get the data. The below VBA is what I have used.

Private Sub Userform_Initialise()

Dim v, e
With Sheets("dropdowns").Range("I2:I500")
    v = .Value
End With
 
With CreateObject("scripting.dictionary")
    .comparemode = 1
For Each e In v
    If Not .exists(e) Then .Add e, Nothing
Next

If .Count Then Me.ComboBox1.List = Application.Transpose(.keys)
End With

This code works for 1 ComboBox. I would like to repeat the above for 2 more Comboboxes from other unique lists.

What needs to be changed in the code above to get this to work?

combobox userform vba excel
1 Answers

You could create a Subroutine that fills one ComboBox with the values of one Range and call this routine 3 times. You can see that the logic to fill didn't change and the only difference is that you pass the range and the comboBox as parameter. Just adapt the ranges and the names of the comboboxes to your needs.

Private Sub UserForm_Initialize()    
    With ThisWorkbook.Sheets("dropdowns")
        FillCombo .Range("I2:A500"), Me.ComboBox1
        FillCombo .Range("J2:J500"), Me.ComboBox2
        FillCombo .Range("K2:K500"), Me.ComboBox3
    End With            
End Sub

Sub FillCombo(r As Range, combobox As Control)
    Dim v, e
    v = r.Value

    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For Each e In v
            If Not .exists(e) Then .Add e, Nothing
        Next
        If .Count Then combobox.List = Application.Transpose(.keys)
    End With
End Sub
FunThomas0
Related