VBA-Sorting the data in a listbox, sort works but data in listbox not changed

Question

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.

Answer 1

You can't pass objects by value. Since you're not going to return another instance of listbox to the caller, you should declare LP as ByVal. That does not affect the code though. It works and the list gets sorted. I think you omitted some importand details.

Answer 2

This works for me on Excel 2003 on a very basic UserForm with a single ListBox called ListBox1:

Private Sub UserForm_Initialize()

ListBox1.AddItem "john"
ListBox1.AddItem "paul"
ListBox1.AddItem "george"
ListBox1.AddItem "ringo"

SortListBox ListBox1

End Sub

and then your SortListBox as written apart from fixing the three comments which start with ! rather than '

The only difference to your initializer is the name (UserForm_Initialize vs InitializeForm). Make sure to use the object and event selectors at the top of the code page for the userform to ensure that the event handlers get named correctly

Answer 3

I dont know if this would work for you but try it this way.

First, make an array of all the items in the list box

Pass that array to your function

Sort that array

return the array to the main program

clear the listbox

overwrite the listbox items with the new array