Jan 14

Using Collections in Excel to populate UserForm Controls

The following is programming code we frequently use to populate a drop-down list or listbox on an Excel UserForm with a unique list of items from a spreadsheet.

Private Sub UserForm1_Intialize()

    'Creates a new collection
    Dim myList As New Collection

    'Determines number of rows to loop through
    numRows = Range("A1").CurrentRegion.Rows.Count

    'Optional - erases existing dropdown list from control
    Me.lstDropdown.Clear

    'Loops through each row and adds to collection
    For i = 2 To numRows

        'An item can only be added to a collection once, hence the on error
        On Error Resume Next
        myList.Add Cells(i, "A"), Cells(i, "A")
        On Error GoTo 0

    Next

    'Populates control with items from the collecton
    For i = 1 To myList.Count

        Me.lstDropdown.AddItem myList.Item(i)

    Next

    'Optional - erases an existing value
    Me.lstDropdown.Value = ""

End Sub

 

Lauren Lummus