Sort Combobox alphabetically vba excel

Question

I'm bulting a small programm with vba Excel. My Comboxbox is filled from an excel sheet. I want to build some controls like "add / delete items" to add other items to my Combobox that why i need to know the row number for each item on my combobox. my code is capable of doing this but i still don't know how to sort the items alphabetically before adding them to the combobox ( see my code below) can you help please ? thanks in advance

Sub Fill_EightD_D1_CB1()

With EightD.EightD_D1_CB1
    .ColumnCount = 2        ' 2 colonnes
    .ColumnWidths = "-1;0"  ' dont une de masquée

End With

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("D1")
Dim LC As Long
Dim i As Long

LC = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

For i = 2 To LC

    If ws.Cells(i, 1) <> "" Then
    EightD.EightD_D1_CB1.AddItem ws.Cells(i, 1).Value
    EightD.EightD_D1_CB1.List(EightD.EightD_D1_CB1.ListCount - 1, 1) = Mid(ws.Cells(i, 1).Address(False, False), 2, 1) 

    End If
Next i

'show always the first element
EightD.EightD_D1_CB1.ListIndex = 0

'Bold Text EightD_D1_CB1
EightD.EightD_D1_CB1.Font.Bold = True

End Sub

Answer 1

Try

Sub Fill_EightD_D1_CB1()

    With EightD.EightD_D1_CB1
        .ColumnCount = 2        ' 2 colonnes
        .ColumnWidths = "-1;0"  ' dont une de masquee

    End With
    Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("D1")
    Dim LC As Long
    Dim i As Long, r As Long, j As Long
    Dim vDB As Variant, vR(), vtemp(1 To 2)

    LC = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    vDB = ws.Range("a2", "a" & LC)
    r = UBound(vDB, 1)
    ReDim vR(1 To r, 1 To 2)
    For i = 1 To r
        vR(i, 1) = vDB(i, 1)
        vR(i, 2) = i + 1
    Next i
    For i = 1 To r
        For j = 1 To r
            If vR(i, 1) < vR(j, 1) Then
                vtemp(1) = vR(i, 1)
                vtemp(2) = vR(i, 2)
                vR(i, 1) = vR(j, 1)
                vR(i, 2) = vR(j, 2)
                vR(j, 1) = vtemp(1)
                vR(j, 2) = vtemp(2)
            End If
        Next j
    Next i
    EightD.EightD_D1_CB1.List = vR
    'show always the first element
    EightD.EightD_D1_CB1.ListIndex = 0
    'Bold Text EightD_D1_CB1
    EightD.EightD_D1_CB1.Font.Bold = True
End Sub

Answer 2

Use a data structure that support sorting to first capture the data, sort it, then add to your Listbox. I've shown how to add and sort in the below code.

Sub SortAnArrayList()
    Dim ArrayList As Object
    Dim ArrayItem As Variant

    Set ArrayList = CreateObject("System.Collections.ArrayList")

    With ArrayList
        .Add "b"
        .Add "c"
        .Add "a"
        .Sort
    End With

    For Each ArrayItem In ArrayList
        Debug.Print ArrayItem
    Next

End Sub