Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting items in a combobox
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting items in a combobox
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" 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
sorting items in a combobox
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to add items to combobox | Excel Programming | |||
Sorting Items in a ComboBox? | Excel Programming | |||
add a range of items to a combobox | Excel Programming | |||
ComboBox Items | Excel Programming | |||
ComboBox items | Excel Programming |