PDA

View Full Version : Sorting a Listbox Data in VBA



ms06f
01-21-2012, 11:03 AM
How to sort a listbox1 column A or column B ?


RowSource =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1,10)
(Dymanic Range)

Can anybody tell me what's wrong?

mdmackillop
01-21-2012, 03:52 PM
You need to sort a multi-dimension array. Have a look here (http://en.allexperts.com/q/Excel-1059/2008/8/Sort-multi-dimensional-array.htm)

Sub QuickSort(SortArray, col, L, R, bAscending)
'
'Originally Posted by Jim Rech 10/20/98 Excel.Programming

' Modifications made by t.w. ogilvy
'Modified to sort on first column of a two dimensional array
'Modified to handle a second dimension greater than 1 (or zero)
'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

ms06f
01-21-2012, 06:48 PM
Sorry, my level too low.....
1. How to use ?
2. & is it only sort the listbox1 display data, sheet1 data never change ?

mikerickson
01-21-2012, 08:06 PM
This will sort the listbox according to the column indicated by the option buttons.
Note that the listbox is not filled via RowSource, but it is dynamic.

Private Sub OptionButton1_Change()
SortListBox
End Sub

Private Sub SortListBox()
Dim i As Long, j As Long, k As Long
Dim compareColumn As Long

compareColumn = IIf(OptionButton1.Value, 0, 4)

With ListBox1
For i = 1 To .ListCount - 1

For j = 0 To i - 1
If StrComp(.List(i, compareColumn), .List(j, compareColumn), 1) = -1 Then
Exit For
End If
Next j

.AddItem .List(i, 0), j
For k = 1 To .ColumnCount - 1
.List(j, k) = .List(i + 1, k)
Next k
.RemoveItem i + 1

Next i
End With

End Sub

Private Sub UserForm_Initialize()
ListBox1.ColumnHeads = False
With Sheet1.Range("A:A")
With Range(.Cells(2, ListBox1.ColumnCount), .Cells(Rows.Count, 1).End(xlUp))
ListBox1.List = .Value
End With
End With
OptionButton1.Value = True
End Sub

Private Sub UserForm_activate()
Application.ShowToolTips = True
With ListBox1
.ControlTipText = "Click the Name"
End With
End Sub

ms06f
01-22-2012, 12:45 AM
Thankyou mikerickon reply my post,
but something wrong in vba script, I can't to solve it , please help me again.
My msoffice is chinese ver.2010

mikerickson
01-22-2012, 01:59 AM
Have you set the RowSource to ""?

My Mac doesn't support RowSource, so my solution assumes that it is "".

ms06f
01-22-2012, 09:07 AM
Yes I set the RowSource =OFFSET(Sheet1!$A$2,,,COUNTA(Sheet1!$A:$A)-1,10) (Dymanic Range)

it same of "A2:J6" (Fix Range)

mikerickson
01-22-2012, 09:27 AM
Set the rowsource to "".
Sorting the ListBox in place (ie. not sorting the source range) precludes the use of RowSource to fill the ListBox