How to sort items in ComboBox (MS Forms 2.0)

[Godelinde]

Hello,

I am using an MS-Forms 2.0 ComboBox, but it has no "sorted" property, like the usual ComboBox has.
Is there any way to alphabetically/numerically sort items in an MS-Forms 2.0 ComboBox?

If not, how would I add items in alphabetical/numerical order to an MS-Forms ComboBox?

Thanks!

 

[Kendel]

If you get data from a table, you can sort items before inserting them into the ComboBox.

Something like:

"SELECT ID, FullName FROM yourTable ORDER by ID"

 

[rmikesmith]

Godelinde,

Your best recourse, as Kendel stated, is to sort the data at the source, if possible. However, if you are adding items to the combo box from, say, a text file, then you should probably put them into an array first, sort the array, then add each item in the array to your combo box. There are many sorting algorithms but here is one example you could use:

Sub ListSort(ByRef Arr() As String, Num As Integer)
'  Sorts an array of strings using the Shell Sort algorithm.

Dim gap, i, j, k As Integer
Dim tmp As String

    gap = Num \ 2
    Do While (gap > 0)
      For i = (gap + 1) To Num
        j = i - gap
        Do While j > 0
          k = j + gap
          If Arr(j) <= Arr(k) Then
            j = 0
          Else
            tmp = Arr(j)
            Arr(j) = Arr(k)
            Arr(k) = tmp
          End If
          j = j - gap
        Loop
      Next i
      gap = gap \ 2
    Loop
      
End Sub

HTH
M. Smith