Sort Combobox list alphabetically

**plans** · 06-13-2015, 12:08 PM

Hello,
I am running this Combobox:

Public Sub UserForm_Initialize()
Dim ListItems As Collection, cell As Range
On Error Resume Next
Set ListItems = New Collection
With ComboBox1
.Clear
For Each cell In Range("ListFruits")
ListItems.Add cell.Value, cell.Value
   .AddItem cell.Value
Next cell
End With
ComboBox1.ListIndex = 1
UserForm1.Show
End Sub

I want to have the Combobox list displayed in alphabetical order, not in the order it is in the ListFruits.

Could anyone advise please?
Thank you

**snb** · 06-13-2015, 12:12 PM

userform.show should not be part of the Initialize event.

Public Sub UserForm_Initialize()
  combobox1.list=Range("ListFruits").value
End Sub

**mikerickson** · 06-13-2015, 12:46 PM

Try

Private Sub UserForm_Initialize()

    Dim oneCell As Range
    Dim i As Long

    ComboBox1.Clear

    For Each oneCell In Range("ListFruit")
        For i = 0 To ComboBox1.ListCount - 1
            If oneCell.Value < ComboBox1.List(i) Then Exit For
        Next i
        ComboBox1.AddItem oneCell.Value, i
    Next oneCell

    ComboBox1.ListIndex = 1
End Sub

**plans** · 06-13-2015, 01:34 PM

Hi mikerickson,
Thanks for the suggestion; I copied the code into Sheet1 Code (attached);
I am getting a Run-time error '1004';
"Method "Range" of object '_Global' failed"
Any idea why?
Thanks