Can a listbox be sorted? - How? Please help

[aldi]

Hello every one, i would like to know if there is a way to sort a listbox? if you know the answer i will greatly appreciate your help.

thanks a lot,

 

[rmikesmith]

aldi,

There is no built-in method for sorting a listbox. There are several approaches you could take, some of which will require VBA programming. If the data is from a worksheet (assuming Excel is the host app), it is probably best to sort it in the worksheet, prior to entering it into the listbox; e.g., Data/Sort... from the menu (this could also be carried out using VBA code). If the data is not in a worksheet, I would put it into an array, sort it, then assign the array to the listbox. You could also sort the listbox directly but this is probably the slowest method (although if the number of items is small it won't matter much).

Post back with more details (which application, source of the data, etc.) if you need more help.


HTH
Mike

 

[aldi]

Hello rmikesmith, thank you for answering.
my listbox is populatated with an array which is loaded from a ws in another wb.

lstPreviousQuote.List() = arrPreviousQuote

i see what you mean by sorting the data in the ws, but the problem is that the ws is in another wb and that would make it slower than just sorting the array and repopulating the listbox. If you think that sorting the array is the best solution could you please hint me on how to sort an array.

thanks again

 

[JESTAR]

Only sorting method I know of is if you read from SQL.

 

[JESTAR]

Sorry, double post...

SQL = "Order by Name"

I know that's not much use, but just run a macro before filling the listbox that sorts the data.

 

[rmikesmith]

aldi,

Here is a sort procedure you can use. You supply the array name and number of elements as parameters. Note: I believe the array subscript must be 1-based to work properly.

Sub ListSort(ByRef Arr() As String, Num As Integer)
'  Sorts an array of strings using the Shell Sort algorithm.

Dim gap, i, j, k As Integer
Dim tmp As String

    gap = Num \ 2
    Do While (gap > 0)
      For i = (gap + 1) To Num
        j = i - gap
        Do While j > 0
          k = j + gap
          If Arr(j) <= Arr(k) Then
            j = 0
          Else
            tmp = Arr(j)
            Arr(j) = Arr(k)
            Arr(k) = tmp
          End If
          j = j - gap
        Loop
      Next i
      gap = gap \ 2
    Loop
      
End Sub

Let us know how this works out.

Regards,
Mike

 

[aldi]

Hi Mike, thank you for giving me the code to sort the list.
One thing I didn't mention was that the array is a 2D arr with 5 columns by 200 rows (for now it is 200 but i will change it to dynamic malloc because the list will keep growing)

arrPreviousQuote(200, 4)

i need to be able to sort the list by column 0, 3, & 4 which will be 1, 4, & 5 when i change the array to base 1

Do you think it will be worth doing it or should i better sort the ws in the remote wb and bring it every time the user decides to sort?

Let me know what you think and thank you very much for your time.

aldi

 

[rmikesmith]

aldi,

Here is a modified ListSort procedure that will handle 2D arrays. The ARows and ACols arguments are the number of row entries and column entries, respectively; for your example this would be 200 and 5. The SortCol argument is the column number to sort on.

Sub ListSort(ByRef Arr() As String, ARows As Long, ACols As Long, SortCol As Long)
'  Sorts a 2D array of strings using the Shell Sort algorithm.

Dim gap, i, j, k, n As Integer
Dim tmp As String

    gap = ARows \ 2
    Do While (gap > 0)
      For i = (gap + 1) To ARows
        j = i - gap
        Do While j > 0
          k = j + gap
          If Arr(j, SortCol) <= Arr(k, SortCol) Then
            j = 0
          Else
            For n = 1 To ACols
              tmp = Arr(j, n)
              Arr(j, n) = Arr(k, n)
              Arr(k, n) = tmp
            Next n
          End If
          j = j - gap
        Loop
      Next i
      gap = gap \ 2
    Loop
      
End Sub

HTH
Mike

 

[aldi]

Hi Mike,
Thank you very much for your help!!!!!!!!!!
I will try it and let you know how it goes.
Thanks again,
aldi