?
Solved

Sorting a Listbox with alot of columns

Posted on 2011-03-21
18
Medium Priority
?
406 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Fordraiders
  • 8
  • 6
  • 2
  • +1
18 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35181250
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.
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 35181265
Listview  vba...dont have one
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35181533
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
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 45

Expert Comment

by:patrickab
ID: 35181558
Place th data first on   a ws, sort it and then add the sorted data to the listbox
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 35181631
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
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35181668
Which column do you want it sorted on?

Sid
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 35181684
I would rather not sort the listbox via the sheet first...then populate the listox again..if at all possible..
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 35181688
columnC
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35181712
So you don't want the actual data to be sorted? Just the listbox sorted?

Sid
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 35181733
SORRY sort by 3RD COLUNN in the listbox,..
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 35181753
sid, correct, sort the listbox...(not the data on the sheet.then reload the sheet...)
...
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 35181923
Is this what you want? Sample file attached. When you run the userform, you will see the list sorted :)

Sid

Code Used

Private Sub UserForm_Initialize()
    Dim usedRng As Range
    Dim ws As Worksheet
    Dim FirstRow As Long, FirstCol As Long, LastRow As Long, LastCol As Long
    Dim vaItems As Variant
    Dim i As Long, j As Long
    Dim c As Integer
    Dim vTemp As Variant
    
    Set ws = Sheets("List")
    
    FirstRow = ws.Cells.Find(What:="*", SearchDirection:=xlNext, SearchOrder:=xlByRows).Row
    
    FirstCol = 1

    LastRow = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
    
    LastCol = ws.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
    
    Set usedRng = ws.Range(ws.Cells(FirstRow, FirstCol), ws.Cells(LastRow, LastCol))
    
    ListBox1.RowSource = "List!" & usedRng.Address
    
    vaItems = ListBox1.List
    
    For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
        For j = i + 1 To UBound(vaItems, 1)
            If vaItems(i, 2) > vaItems(j, 2) Then
                For c = 0 To ListBox1.ColumnCount - 1 'Allows sorting of multi-column ListBoxes
                    vTemp = vaItems(i, c)
                    vaItems(i, c) = vaItems(j, c)
                    vaItems(j, c) = vTemp
                Next c
            End If
        Next j
    Next i
    
    ListBox1.RowSource = ""
    ListBox1.List = vaItems
End Sub

Open in new window

example-data-for-listbox.xls
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 35181949
FWIW, the Listview is available from the MS Windows Common Controls library.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35182265
fordraiders: So did it work?

Sid
0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 35182394
Worked GREAT sid  Thanks..
0
 
LVL 3

Author Comment

by:Fordraiders
ID: 35183585
Sid, Just to verify, Is the code you posted sorting by column 2 or 3 ?
0
 
LVL 45

Expert Comment

by:patrickab
ID: 35184360
>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

0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35186680
>>> 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
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question