ComboBox Sorting

[zaahir]

Hi

I enetr data off a spreadsheet(DB) into a combobox....but the data entered does not go in in alphabetical order....
which code can i use to sort the data alphabetically?
Regards

 

[Andrew Poulsom]

Here is an example which you may be able to adapt:

Private Sub UserForm_Initialize()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Arr As Variant
    Dim i As Long
    Dim j As Long
    Dim Temp As Variant
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1:A" & Sh.Range("A" & Sh.Rows.Count).End(xlUp).Row)
    ComboBox1.Clear
    Arr = WorksheetFunction.Transpose(Rng.Value)
    For i = LBound(Arr) To UBound(Arr) - 1
        For j = i + 1 To UBound(Arr)
            If Arr(i) > Arr(j) Then
                Temp = Arr(j)
                Arr(j) = Arr(i)
                Arr(i) = Temp
            End If
        Next j
    Next i
    For i = 1 To UBound(Arr)
        ComboBox1.AddItem Arr(i)
    Next i
End Sub

Note it won't work if your ComboBox has the RowSource property set.