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.