Populate a MultiColumn ListBox From an Unsorted Range
In Sorting a Multicolumn ListBox, I give an example of how to sort a ListBox with two columns. If you have two or three columns, this example is as good as any. However, when you have a lot of columns, the bubble sort can get a little unwieldy. This is the perfect time to use Excel’s built in sort feature.
Assume you have a five column range on a worksheet that you need to leave unsorted (or sorted on some other key), like an External Data Range.
This is part of an External Data Query from Northwind’s Customer table that is unsorted. To put all these columns in a ListBox sorted on the City field, I copy this range to a new worksheet, sort the Range, then use the ListBox’s List property to put the sorted values into the ListBox. Finally, I delete the temporary worksheet.
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Dim rCopy As Range
Me.ListBox1.ColumnCount = 5
‘Add a temporary worksheet to sort a range
With ThisWorkbook
Set ws = .Worksheets.Add(after:=.Sheets(.Sheets.Count))
End With
‘Define the range to copy to the new worksheet
‘but exclude the header row
With Sheet1.QueryTables(1).ResultRange
Set rCopy = .Offset(1).Resize(.Rows.Count - 1)
End With
rCopy.Copy Destination:=ws.Range(”A1″)
‘Sort the copied range
ws.Range(”A1″).CurrentRegion.Sort ws.Range(”C1″), xlAscending
‘Populate the listbox
Me.ListBox1.List = ws.Range(”A1″).CurrentRegion.Value
‘Delete the temporary worksheet
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
End Sub
The ListBox looks like this
Betsy:
Hi there! I enjoy your site, you have a lot of great information! I have a question for you though…
I’m doing something similar to this, but was wondering if it’s possible to sort the data in the listbox, while in the listbox itself, say, by clicking on the different headings of columns? Any ideas? Thanks!
14 January 2005, 1:52 pmDick:
Betsy: Thanks. That should be possible. It’s hard to determine columns when clicking in a listbox, or so I’ve found. Rows are easy, but not so much for columns. I’ll mess around with it and see if I can come up with something useful.
14 January 2005, 8:22 pmJake Marx:
Betsy,
When you have to deal with different alignments per column or other column-related activities, you should look at the ListView control. I’m not sure if it’s included in Windows or if it comes with Visual Studio (or VB). But if you have it on your machine, you may want to take a look at it.
I have a sample workbook if you’d like me to email it to you.
17 January 2005, 3:53 pmtony:
I am use less
2 June 2005, 8:24 amDave:
Betsy,
I would add a row of CommandButtons just above the listbox that would look like headers (headers are a good idea anyway). They could pass the column to sort on as a parameter to Dick’s code. You could even toggle ascending and descending sorts with successive clicks.
Dave
16 November 2005, 4:15 pmJon Peltier:
I actually do this with a row of labels. I give the labels the button type appearance and put the sort into their on-click event. The label buttons make less obtrusive headers than command buttons.
17 November 2005, 5:56 amPaCoCourt:
Hello,
It’s verry fast to sort with your code.
In the listbox, i’d like to modify an element (a colomn from a row)Or
the most interesting, is to paste directly data in a raw.
It’s verry kind if you could help me.
bye
29 November 2006, 8:54 amP.
Antoine Dubuc:
Hi!
Thank you for this website. It is full of good ideas. Publish a book! (or did you?)
I have a question…
If the data is just a worksheet, then what changes in this code?
Also, whats the difference between a worksheet and a sheet!!!!!????
Best regards,
Antoine Dubuc
8 December 2006, 10:58 pmPradeep:
Hi,
i am software engg working in tokyo,japan.
in my project i have to sort the list coloumn if its corresponding header is clicked.
i need its solution as soon as possible,it is very very urgently required.
please help me with the solution.
rgds
18 January 2007, 6:47 pmpradeep
hui:
Hi ,
I tried the code. But this portion is not working:
With Sheet1.QueryTables(1).ResultRange
Set rCopy = .Offset(1).Resize(.Rows.Count - 1)
End With
Could anyone help?
2 March 2007, 2:11 amGeb Blum:
Thank you so much. This article has been a great help!
5 April 2008, 3:26 pm