Solved

Sorting a Listbox with alot of columns

Posted on 2011-03-21
18
359 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
Comment Utility
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
Comment Utility
Listview  vba...dont have one
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
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
Comment Utility
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
Comment Utility
Which column do you want it sorted on?

Sid
0
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
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
Comment Utility
columnC
0
 
LVL 30

Expert Comment

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

Sid
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Author Comment

by:fordraiders
Comment Utility
SORRY sort by 3RD COLUNN in the listbox,..
0
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
sid, correct, sort the listbox...(not the data on the sheet.then reload the sheet...)
...
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 500 total points
Comment Utility
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
Comment Utility
FWIW, the Listview is available from the MS Windows Common Controls library.
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
fordraiders: So did it work?

Sid
0
 
LVL 3

Author Closing Comment

by:fordraiders
Comment Utility
Worked GREAT sid  Thanks..
0
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
Sid, Just to verify, Is the code you posted sorting by column 2 or 3 ?
0
 
LVL 45

Expert Comment

by:patrickab
Comment Utility
>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
Comment Utility
>>> 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now