sorting items in a combobox

[BartH]

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.

 

[Tom Ogilvy]

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.

 

[BartH]

Hi Tom,

Thanks! Your code does what you promissed; the sorting is fine. But when I
try to merge it with my code (I need to get unique items) I either get an
error or there is no more sorting...

This was my original code:

With Me.ComboBox2
.Clear ' clear the listbox content
MyUniqueList3 = UniqueItemList(Range("genres"), True)

For g = 1 To UBound(MyUniqueList3)
.AddItem MyUniqueList3(g)
Next g
.ListIndex = 0 ' select the first item
End With

I would say this should work...

Dim MyUniqueList3 As Variant, g As Long
Dim i As Long, j As Long, v As Variant
Dim tmp As Variant

With Me.ComboBox2
.Clear ' clear the listbox content
' this is a function that give unique items, "genres" is my named
range
MyUniqueList3 = UniqueItemList(Range("genres"), True)
v = MyUniqueList3
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
.List = v
End With

But then I get an error code # 9 "Subscript out of range" (or something
alike - I use a Dutch version)

With the following code there are no errors, but no sorting too...

With Me.ComboBox2
.Clear ' clear the listbox content
MyUniqueList3 = UniqueItemList(Range("genres"), True)

For g = 1 To UBound(MyUniqueList3)
.AddItem MyUniqueList3(g)
Next g

' sorting by Tom Ogilvy
Dim i As Long, j As Long, v As Variant
Dim tmp As Variant
v = .List
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
.List = v
.ListIndex = 0 ' select the first item
End With

I must be overlooking something...

Regards,
Bart

 

[BartH]

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, before:=j
NoDupes.Add Swap2, before:=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