sort items of listboxes with multiple columns

[borg]

Hi,

I have 2 questions that are related.

I would like to sort the items in a listbox that contains 3 columns. What
is the code to sort by the first column, 2nd column, and 3rd column? Or do I
have to sort the data first before I put it into the columns of the listbox?

Secondly, is there a way to sort the values in an array after the values
have been stored in the array?

Thank you for the help!

[Tom Ogilvy]

There is no built in support for sorting data in listboxes, so you would
have to either sort it before putting it in, sort it as you put it in, or
sort it in the list.

Here is some code to sort an array. .

The second procedure shows how to call it.

Sub QuickSort(SortArray, col, L, R, bAscending)
'
'Originally Posted by Jim Rech 10/20/98 Excel.Programming
'Modified to sort on first column of a two dimensional array
'Modified to sort on single column
'Modified to do Ascending or Descending
Dim i, j, X, Y, mm

i = L
j = R
X = SortArray((L + R) / 2, col)
If bAscending Then
While (i <= j)
While (SortArray(i, col) < X And i < R)
i = i + 1
Wend
While (X < SortArray(j, col) And j > L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
Else
While (i <= j)
While (SortArray(i, col) > X And i < R)
i = i + 1
Wend
While (X > SortArray(j, col) And j > L)
j = j - 1
Wend
If (i <= j) Then
For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
Y = SortArray(i, mm)
SortArray(i, mm) = SortArray(j, mm)
SortArray(j, mm) = Y
Next mm
i = i + 1
j = j - 1
End If
Wend
End If
If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending)
If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending)
End Sub




Sub aaTesterSort()
Dim bAscending As Boolean
Set rng = Range("I7").CurrentRegion
vArr = rng.Value
bAscending = False
QuickSort vArr, 5, LBound(vArr, 1), UBound(vArr, 1), bAscending
Range("I26").Resize(UBound(vArr, 1), UBound(vArr, 2)).Value = vArr
End Sub

--
Regards,
Tom Ogilvy

"borg" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I have 2 questions that are related.
>
> I would like to sort the items in a listbox that contains 3 columns. What
> is the code to sort by the first column, 2nd column, and 3rd column? Or
do I
> have to sort the data first before I put it into the columns of the
listbox?
>
> Secondly, is there a way to sort the values in an array after the values
> have been stored in the array?
>
> Thank you for the help!

[borg]

Thank you very much!

"Tom Ogilvy" wrote:

> There is no built in support for sorting data in listboxes, so you would
> have to either sort it before putting it in, sort it as you put it in, or
> sort it in the list.
>
> Here is some code to sort an array. .
>
> The second procedure shows how to call it.
>
> Sub QuickSort(SortArray, col, L, R, bAscending)
> '
> 'Originally Posted by Jim Rech 10/20/98 Excel.Programming
> 'Modified to sort on first column of a two dimensional array
> 'Modified to sort on single column
> 'Modified to do Ascending or Descending
> Dim i, j, X, Y, mm
>
> i = L
> j = R
> X = SortArray((L + R) / 2, col)
> If bAscending Then
> While (i <= j)
> While (SortArray(i, col) < X And i < R)
> i = i + 1
> Wend
> While (X < SortArray(j, col) And j > L)
> j = j - 1
> Wend
> If (i <= j) Then
> For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
> Y = SortArray(i, mm)
> SortArray(i, mm) = SortArray(j, mm)
> SortArray(j, mm) = Y
> Next mm
> i = i + 1
> j = j - 1
> End If
> Wend
> Else
> While (i <= j)
> While (SortArray(i, col) > X And i < R)
> i = i + 1
> Wend
> While (X > SortArray(j, col) And j > L)
> j = j - 1
> Wend
> If (i <= j) Then
> For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
> Y = SortArray(i, mm)
> SortArray(i, mm) = SortArray(j, mm)
> SortArray(j, mm) = Y
> Next mm
> i = i + 1
> j = j - 1
> End If
> Wend
> End If
> If (L < j) Then Call QuickSort(SortArray, col, L, j, bAscending)
> If (i < R) Then Call QuickSort(SortArray, col, i, R, bAscending)
> End Sub
>
>
>
>
> Sub aaTesterSort()
> Dim bAscending As Boolean
> Set rng = Range("I7").CurrentRegion
> vArr = rng.Value
> bAscending = False
> QuickSort vArr, 5, LBound(vArr, 1), UBound(vArr, 1), bAscending
> Range("I26").Resize(UBound(vArr, 1), UBound(vArr, 2)).Value = vArr
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "borg" <[email protected]> wrote in message
> news:[email protected]...
> > Hi,
> >
> > I have 2 questions that are related.
> >
> > I would like to sort the items in a listbox that contains 3 columns. What
> > is the code to sort by the first column, 2nd column, and 3rd column? Or
> do I
> > have to sort the data first before I put it into the columns of the
> listbox?
> >
> > Secondly, is there a way to sort the values in an array after the values
> > have been stored in the array?
> >
> > Thank you for the help!
>
>
>