alphabetize listBox

[tonyBruckner]

How do I get the addItems in a listBox to be laid out alphabetically?

I'm filling out the listBox using a For statement that moves through a large assembly of names (Doe,John). The assembly is a seating chart and consequently cannot be put in order on the spreadsheet. Names that pass certain conditions in the loop are added to the listBox.

How and where in this system can I alphabetize the listBox?

[XL Pro]

They're a several ways to accomplish this. One is to port the list to a worksheet, sort it there, then reimport the items into the Listbox.
Another way is to read the items into a string array, use the below "bubble sort" routine, and re-add the items to the listbox.

Sub BubbleSort(List() As String)
    '   Sorts the List Array in ascending order
    Dim First As Integer, Last As Integer
    Dim i As Integer, J As Integer
    Dim Temp
    First = LBound(List)
    Last = UBound(List)
    For i = First To Last - 1
        For J = i + 1 To Last
            If List(i) > List(J) Then
                Temp = List(J)
                List(J) = List(i)
                List(i) = Temp
            End If
        Next J
    Next i
End Sub

For performance issues, collections can be used, but I don't have that code handy.

[smozgur]

Use this codes in a sample userform with a listbox. Then you can apply it for your userform.

Private Sub UserForm_Activate()
Dim i As Integer
Dim a

'Here we are creating the listbox with data
'You already have this step, we are doing this to have a sample listbox
a = Split("XX,A,RR,LL", ",")
For i = 0 To UBound(a)
ListBox1.AddItem a(i)
Next i

'Now sorting is begining
ReDim oldData(ListBox1.ListCount) As String
Dim sortedData() As String

'Set an array values with the listbox values to use in bubble sort
For i = 0 To ListBox1.ListCount - 1
oldData(i) = ListBox1.List(i)
Next i

'Call the sort routine
sortedData = BubbleSort(oldData)

'Refreshing listbox with sorted data
ListBox1.Clear
For i = 0 To UBound(sortedData)
If Len(Trim(sortedData(i))) > 0 Then ListBox1.AddItem sortedData(i)
Next i

End Sub


Public Function BubbleSort(Strings() As String) As String()
Dim a As Long, b As Long, c As Long, d As Long
Dim e As Integer, f As Integer, g As Integer
Dim i As String, j As String
Dim m() As String, n() As String

e = 1
n = Strings
Do While e <> -1

For a = 0 To UBound(Strings) - 1
i = n(a)
j = n(a + 1)
f = StrComp(i, j)
If f <= 0 Then
n(a) = i
n(a + 1) = j
Else
n(a) = j
n(a + 1) = i
g = 1
End If
Next a
If g = 1 Then
e = 1
Else
e = -1
End If
g = 0
Loop
BubbleSort = n
End Function


regards

[nisht]

Sorting is most discussed topic in world of programming .. this the sentence written by
'mark harris' whom book i refer for my difficulties.

'bubble sort method' as described here is the method mostly used... i presume.
there other methods also for sorting..

like,,
insertion sorts..
ripple sort
shell sort
recursive method of sorting...

well gone to far..

download my file nos..8 'sheet name'

it is example file exactly what you need
nishith desai
http://www.pexcel.com