What you've given me there works well, I did have to tweak it slightly though as the names were just for example and your code told the combobox to start with the name "Anonymous" showing. I have altered the code to look at F27 so that the word "Name" becomes that first entry.
I'm puzzled though, because I didn't touch some of the code that refers to "anonymous" in the userform initialize sub, and even when i changed the name anonymous in my table to something else, there was no error - what is
If MyArray(Count, 1) <> "Anonymous" Then UniqueItem.Add CStr(MyArray(Count, 1)), CStr(MyArray(Count, 1))
doing?
Private Sub Combobox1_Change()
'Find All Matching Entries
ListBox1.Clear ' added this to clear listbox when new name selected
Set rngLook = Range("F27:F" & Range("A40000").End(xlUp).Row) 'changed to F27
strValueToPick = ComboBox1.Value
With rngLook
Set rngFind = .Find(strValueToPick, .Cells(1, 1), LookIn:=xlValues, lookat:=xlWhole)
If Not rngFind Is Nothing Then
strFirstAddress = rngFind.Address
Set rngPicked = rngFind
Do
Set rngPicked = Union(rngPicked, rngFind)
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstAddress
End If
End With
If Not rngPicked Is Nothing Then
rngPicked.Offset(0, -3).Select
'Fill Listbox
For Each c In Selection
ListBox1.AddItem c.Value
Next
End If
End Sub
Private Sub UserForm_Activate()
Dim UniqueItem As Collection, cell As Range, N As Long
'Read your list of names
MyArray = Range("F27:F" & Range("A40000").End(xlUp).Row).Value ' changed to F27
'Remove Duplicates
Set UniqueItem = New Collection
UniqueItem.Add "Name", "Name" ' Changed from Anonymous to Name
On Error Resume Next
For Count = 1 To UBound(MyArray)
If MyArray(Count, 1) <> "Anonymous" Then UniqueItem.Add CStr(MyArray(Count, 1)), CStr(MyArray(Count, 1))
Next
On Error GoTo 0
For Count = 1 To UniqueItem.Count
ComboBox1.AddItem UniqueItem(Count)
Next
ComboBox1.ListIndex = 0
End Sub
Bookmarks