Sort Combobox VBA

Question

I have been thinking how to sort the values in a combobox.

I add items to the ComboBox when I initilize the form because the number of values are constantly increasing on a sheet.

I use the next code to add the items:

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 thought to copy the values that I am going to add on the ComoBox to another sheet and there sort them in the new sheet, it works fine but it doesn't seem to be a smart option, meaning that I create another sheet and then copy the values and sort them instead of sorting them directly.

My question is, anyone knows how to do it directly from the original sheet? I dont know anything of API so, please, only VBA code. I alredy check on MSDN but I can't figure out how to make it work.

Thanks and if more info is needed, please, let me know it.

PS: I cant sort them directly from the original sheet because this Sheet has to be with a static order

Solution

You can read the values from the sheet into an array, sort this with code and then add the items.

This code will do this, using a 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