asked on

Sorting a Listbox with alot of columns

excel 2003

I have a userform with a list box that is being populated from and access database when the useform opens.

The listbox1 has 29 columns :
40 pt;80 pt;150 pt;200 pt;24.95 pt;15 pt;0 pt;80 pt;150 pt;0 pt;40 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt;0 pt

I'm choosing only to see 9 columns

Problem:
I need a command button to sort the listbox1..by column 3 ; Descending

This code does not seem to work properly...
Dim i As Long
Dim j As Long
Dim Temp As Variant
With ListBox1
For i = 0 To .ListCount - 2
For j = i + 1 To .ListCount - 1
If .List(i) > .List(j) Then
Temp = .List(j)
.List(j) = .List(i)
.List(i) = Temp
End If
Next j
Next i
End With

fyi, I do not want the oppulate the listbox from access sql...it needs to be changed via the command button on the userform...to be flexible for the user..

Thanks
fordraiders

Rory Archibald

If you can use a ListView instead, the sorting is a piece of cake. Otherwise, you need to loop through every column of the list for each row that you swap.

Fordraiders

ASKER

Listview vba...dont have one

SiddharthRout

Copy the data to a blank sheet and use text to columns to separate them into Column using ";" as a delimiter and then simply use Excel to sort them :)

If you have a sample workbook then maybe I can give you a sample code?

Sid

patrickab

Place th data first on a ws, sort it and then add the sorted data to the listbox

Fordraiders

ASKER

ok the data is on a sheet called "sheet1"
With the listbox1

Public Sub LoadData()
On Error Resume Next
Dim usedRng As Range
Sheets("List").Activate
' get the rnge from the spreadsheet
DetermineUsedRange usedRng
' select the range for the listbox
ListBox1.RowSource = "List!" & usedRng.Address
End Sub
Sub DetermineUsedRange(ByRef theRng As Range)
Dim FirstRow As Integer, FirstCol As Integer, _
LastRow As Integer, LastCol As Integer
On Error GoTo handleError
FirstRow = Cells.Find(What:="*", _
SearchDirection:=xlNext, _
SearchOrder:=xlByRows).Row
FirstCol = 1
'FirstCol = Cells.Find(What:="1", _
' SearchDirection:=xlNext, _
' SearchOrder:=xlByColumns).Column

LastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
LastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
Set theRng = Range(Cells(FirstRow, FirstCol), _
Cells(LastRow, LastCol))
handleError:
End Sub

This is how I'm loading data into the listview now...
the data is being placed onto the sheet and the code is reading from the sheet to listbox1...

example data in file..
example-data-for-listbox.xls

SiddharthRout

Which column do you want it sorted on?

Sid

Fordraiders

ASKER

I would rather not sort the listbox via the sheet first...then populate the listox again..if at all possible..

Fordraiders

ASKER

columnC

SiddharthRout

So you don't want the actual data to be sorted? Just the listbox sorted?

Sid

Fordraiders

ASKER

SORRY sort by 3RD COLUNN in the listbox,..

Fordraiders

ASKER

sid, correct, sort the listbox...(not the data on the sheet.then reload the sheet...)
...

ASKER CERTIFIED SOLUTION

SiddharthRout

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rory Archibald

FWIW, the Listview is available from the MS Windows Common Controls library.

SiddharthRout

fordraiders: So did it work?

Sid

Fordraiders

ASKER

Worked GREAT sid Thanks..

Fordraiders

ASKER

Sid, Just to verify, Is the code you posted sorting by column 2 or 3 ?

patrickab

>I would rather not sort the listbox via the sheet first...then populate the listox again..if at all possible..

Automate th whole process - that would make it simple and fast

SiddharthRout

>>> Sid, Just to verify, Is the code you posted sorting by column 2 or 3 ?

In Listbox, the columns are start with 0. I have sorted the 2nd column (which is 3rd Col based on this 0-1-2)

Sid