[SOLVED] sorting items in a combobox

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.

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" <nedcom-geenspam-@wanadoo.nl> wrote in message
news:440ee91c$0$48068$dbd4b001@news.wanadoo.nl...
> 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.
>
>

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

"Tom Ogilvy" <twogilvy@msn.com> schreef in bericht
news:u69NOSsQGHA.2436@TK2MSFTNGP11.phx.gbl...
> 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" <nedcom-geenspam-@wanadoo.nl> wrote in message
> news:440ee91c$0$48068$dbd4b001@news.wanadoo.nl...
>> 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.
>>
>>
>
>

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

"Tom Ogilvy" <twogilvy@msn.com> schreef in bericht
news:u69NOSsQGHA.2436@TK2MSFTNGP11.phx.gbl...
> 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" <nedcom-geenspam-@wanadoo.nl> wrote in message
> news:440ee91c$0$48068$dbd4b001@news.wanadoo.nl...
>> 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.
>>
>>
>
>