Sort Combobox VBA

Question

I was thinking how to sort the values ​​in a combo box.

I add elements to the ComboBox when the form is initialized, because the number of values ​​is constantly increasing on the sheet.

I use the following code to add elements:

With ComboBox1 lastcell = ThisWorkbook.Sheets("1").Range("F1000000").End(xlUp).Row + 1 For i = 2 To lastcell .AddItem ThisWorkbook.Sheets("1").Cells(i, 6) Next i End With 

I decided to copy the values ​​that I’m going to add to the ComoBox to another sheet, and sort them on a new sheet, it works fine, but this does not seem to be a reasonable option, which means that I create another one then copy the values ​​and sort them, not sorting them directly.

My question is: does anyone know how to do this directly from the original sheet? I don't know anything about the API, so please only VBA code. I am checking MSDN, but cannot figure out how to make it work.

Thank you, and if you need more information, let me know.

PS: I cannot sort them directly from the source sheet, because this sheet must be with a static order

Answer 1

You can read the values ​​from the sheet into an array, sort it using code, and then add elements.

This code will do this using Quicksort:

 Private Sub UserForm_Initialize() Dim varRange() As Variant Dim lngLastRow As Long Dim i As Long lngLastRow = Range("F:F").Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row varRange = Range("F:F").Resize(lngLastRow).Cells subQuickSort varRange Me.ComboBox1.List = varRange End Sub Public Sub subQuickSort(var1 As Variant, _ Optional ByVal lngLowStart As Long = -1, _ Optional ByVal lngHighStart As Long = -1) Dim varPivot As Variant Dim lngLow As Long Dim lngHigh As Long lngLowStart = IIf(lngLowStart = -1, LBound(var1), lngLowStart) lngHighStart = IIf(lngHighStart = -1, UBound(var1), lngHighStart) lngLow = lngLowStart lngHigh = lngHighStart varPivot = var1((lngLowStart + lngHighStart) \ 2, 1) While (lngLow <= lngHigh) While (var1(lngLow, 1) < varPivot And lngLow < lngHighStart) lngLow = lngLow + 1 Wend While (varPivot < var1(lngHigh, 1) And lngHigh > lngLowStart) lngHigh = lngHigh - 1 Wend If (lngLow <= lngHigh) Then subSwap var1, lngLow, lngHigh lngLow = lngLow + 1 lngHigh = lngHigh - 1 End If Wend If (lngLowStart < lngHigh) Then subQuickSort var1, lngLowStart, lngHigh End If If (lngLow < lngHighStart) Then subQuickSort var1, lngLow, lngHighStart End If End Sub Private Sub subSwap(var As Variant, lngItem1 As Long, lngItem2 As Long) Dim varTemp As Variant varTemp = var(lngItem1, 1) var(lngItem1, 1) = var(lngItem2, 1) var(lngItem2, 1) = varTemp End Sub 

Answer 2

It depends on the circumstances, type and structure of the data. But I prefer to do it like this:
Alternatively, you can use an array and a bubble sorting algorithm :)
change the code a little according to your case

 Option Explicit Sub WITH_COMBOBOX() Dim i As Long Dim arr() As String Dim lastCell As Long lastCell = 500 ReDim arr(lastCell) Call FillAndSortArray(arr) For i = 2 To lastCell .AddItem arr(i - 2) Next i End Sub Sub FillAndSortArray(ByRef myArray() As String) Dim i As Long For i = LBound(myArray) To UBound(myArray) myArray(i) = CStr(ThisWorkbook.Sheets(1).Range("F" & i + 2).Value) Next i Call BubbleSort(myArray) End Sub Sub BubbleSort(ByRef myArray() As String) Dim i As Long, j As Long Dim Temp As String For i = LBound(myArray) To UBound(myArray) - 1 For j = i + 1 To UBound(myArray) - 1 If myArray(i) > myArray(j) Then Temp = myArray(j) myArray(j) = myArray(i) myArray(i) = Temp End If Next j Next i End Sub 

Answer 3

Try entering the code:

 Sub GetAction() Dim rng As Range, lastcell As Long lastcell = Range("F1000").End(xlUp).Row + 1 Set rng = Range("F1:F" & lastcell) ' assuming to start from F1 If Not rng Is Nothing Then rng.Sort Range("F1") ComboBox1.ListFillRange = rng.Address End If End Sub 

Answer 4

to sort 123 for a number

 For Each cell In ThisWorkbook.Sheets("sheet1").Range("list1") Me.ComboBox1.AddItem cell Next cell With Me.ComboBox1 For x = LBound(.list) To UBound(.list) For y = x To UBound(.list) If .list(y, 0) + 0 < .list(x, 0) + 0 Then blah = .list(y, 0) .list(y, 0) = .list(x, 0) .list(x, 0) = blah End If Next y Next x End With 

to sort text abcd

 For Each cell In ThisWorkbook.Sheets("sheet1").Range("list1") Me.ComboBox1.AddItem cell Next cell With Me.ComboBox1 For x = LBound(.list) To UBound(.list) For y = x To UBound(.list) If .list(y, 0) < .list(x, 0) Then blah = .list(y, 0) .list(y, 0) = .list(x, 0) .list(x, 0) = blah End If Next y Next x End With