Solved

Sorting a Listbox with alot of columns

Posted on 2011-03-21
18
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 500 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
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 how to use a scrolling table in Microsoft Excel using the INDEX function.

726 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