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

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.

Up vote 2 down vote favorite share g+ share fb share tw.

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. Excel-vba link|improve this question asked Dec 5 '08 at 22:01Mike Clemens.

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.

I don't 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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.