Here I found a variation by J. Walkenbach:

Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection
Dim i As Integer, j As Integer
Dim Swap1, Swap2, Item

' The items are in A1:A105 - changed this to my range
Set AllCells = Range("genres")

' The next statement ignores the error caused
' by attempting to add a duplicate key to the collection.
' The duplicate is not added - which is just what we want!
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

' Resume normal error handling
On Error GoTo 0

' Sort the collection (optional)
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) NoDupes(j) Then
Swap1 = NoDupes(i)
Swap2 = NoDupes(j)
NoDupes.Add Swap1, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

' Add the sorted, non-duplicated items to a ListBox
For Each Item In NoDupes
Me.ComboBox2.AddItem Item
' UserForm1.ListBox2.AddItem Item
Next Item

Me.ComboBox2.ListIndex = 0 ' select the first item

This works fine, so I have a working solution now but I remain curious about
how I can get Tom's solution to merge with my unique function...

Thanks,
Bart

"Tom Ogilvy" schreef in bericht
...
Dim i as Long, j as Long, v as Variant
Dim tmp as Variant
v = Range("B5:B30").Value
for i = 1 to ubound(v,1) -1
for j = i + 1 to ubound(v,1)
if v(i,1) v(j,1) then
tmp = v(i,1)
v(i,1) = v(j,1)
v(j,1) = tmp
end if
Next
Next
Combobox1.List = v

This uses the slow (but easy to code) bubble sort. Unless you have a lot
of
elements in the list, this shouldn't have much of an impact.

Make sure you don't have a Rowsource or listfillrange property assigned in
your combobox.

--
Regards,
Tom Ogilvy




"BartH" wrote in message
...
Can somebody please provide a brief example of how to sort items in a
combobox? I retrieve the items from an Excel range on a worksheet but I
can't sort in the sheet as these values are part of an other sorted list.