Listbox in Userform

Question

I have a userform for Excel that has a listbox for employee names. I'm sourcing the options from column A of an Excel worksheet so it can auto-complete entries in the userform based on past entries. The problem is that there are multiple rows of entries in the worksheet for each employee and I would like to only have one of each names in the drop-down list.

The code to populate the listbox is:

Private Sub UserForm_Initialize()
    Me.txtName.List = Worksheets("Sheet1").Range("A6:A600").Value

Is there a way to do this?

If ListBox1.Range.Value...?

Answer 1

The below code uses a helper Dictionary object to determine if items have been added to the ListBox, and if not, it adds them. It also dynamically selects the range of names based on a starting cell of A6 and moving down the spreadsheet to the first break in data. If there are breaks that you want to ignore, please let us know.

I used the AddItem method of the ListBox instead of the List property.

Private Sub UserForm_Initialize()
    Dim rNames As Range
    Dim oDict As Object
    Dim cel As Range

    Set rNames = Worksheets("Sheet1").Range("A6:A" & Worksheets("Sheet1").Range("A6").End(xlDown).Row)
    Set oDict = CreateObject("Scripting.Dictionary")

    For Each cel In rNames
        If oDict.exists(cel.Value) Then
            'Do nothing for now
        Else
            oDict.Add cel.Value, 0
            Me.txtName.AddItem cel.Value
        End If
    Next cel
End Sub