Sorting within an array, or in a combobox

**JP Romano** · 05-31-2011, 12:45 PM

SNB, as I'm being asked to do more and more with Excel/VBA here at work, I try to find better, faster, smarter ways of doing things with each effort. With this little project, I wanted to force myself to use arrays, which are very new and interesting to me. My interest wasn't only in the end point, but different ways to get there. Hope that makes sense, and as always, thanks for taking the time to read and offer something incredible!

**shg** · 05-31-2011, 01:06 PM

One more alternative:

Sub demo()
    Sheet1.ComboBox1.List = Range("A2:A11").Value
    SortTheBox Sheet1.ComboBox1
    
    Sheet1.ListBox1.List = Range("B2:B11").Value
    SortTheBox Sheet1.ListBox1
End Sub

Function SortTheBox(vCtl As Variant) As Boolean
    Dim i         As Long
    Dim j         As Long

    If Not IsObject(vCtl) Then Exit Function

    Select Case TypeName(vCtl)
        Case "ComboBox", "ListBox"
            With vCtl
                For i = 1 To .ListCount - 1
                    For j = 0 To i - 1
                        If .List(i) < .List(j) Then
                            .AddItem .List(i), j
                            .RemoveItem i + 1
                            Exit For
                        End If
                    Next j
                Next i
            End With

            SortTheBox = True
    End Select
End Function