VBA-Sorting the data in a listbox, sort works but data in listbox not changed
- by Mike Clemens
A listbox is passed, the data placed in an array, the array is sort and then the data is placed back in the listbox. The part that does work is putting the data back in the listbox. Its like the listbox is being passed by value instead of by ref.
Here's the sub that does the sort and the line of code that calls the sort sub.
Private Sub SortListBox(ByRef LB As MSForms.ListBox)
Dim First As Integer
Dim Last As Integer
Dim NumItems As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As String
Dim TempArray() As Variant
ReDim TempArray(LB.ListCount)
First = LBound(TempArray) ' this works correctly
Last = UBound(TempArray) - 1 ' this works correctly
For i = First To Last
TempArray(i) = LB.List(i) ' this works correctly
Next i
For i = First To Last
For j = i + 1 To Last
If TempArray(i) > TempArray(j) Then
Temp = TempArray(j)
TempArray(j) = TempArray(i)
TempArray(i) = Temp
End If
Next j
Next i ! data is now sorted
LB.Clear ! this doesn't clear the items in the listbox
For i = First To Last
LB.AddItem TempArray(i) ! this doesn't work either
Next i
End Sub
Private Sub InitializeForm()
' There's code here to put data in the list box
Call SortListBox(FieldSelect.CompleteList)
End Sub
Thanks for your help.
