Sort a multi column listbox in userform

[Martin]

I have a 4 column listbox in a userform. I would like to sort the contents
by pressing a command button above the column to be sorted. Can I do so and
if so how do I do it.

Thank You,

 

[Tom Ogilvy]

How do you populate the listbox. If using the rowsource, try sorting the
source data in the worksheet, then perhaps reassign the rowsource if
necessary.

 

[Martin]

Tom,

I have a datasource of about 2000 rows referencing insurance classes.
Listbox3 consists of the major catagories of insurance ie: property,
Liability, etc (about 20 catagories). Listbox4 populates from the datasource
- listing the courses, credithours, type of instruction, and instructors,
based on the criteria selected in listbox4.

here is mycode for lisbox3_click
Private Sub ListBox3_Click()
Dim x As Integer, rw As Long, l As Integer
Dim c As Range
Dim mytest As String, mycol As Integer, mylookup As String
ListBox4.Clear
TextBox7.Text = ""
TextBox8.Text = ""
TextBox9.Text = ""
ListBox4.ColumnCount = 4
ListBox4.ColumnWidths = "190;40;47;14"

mytest = ListBox3.Value
mycol = ListBox3.ListIndex + 19
mylookup = "1"
With Worksheets("PC Analysis").Columns(mycol)
Set c = .Find(mylookup, LookIn:=xlValues, MatchCase:=False)
If Not c Is Nothing Then

firstaddress = c.address
Do
temp = c.address
l = Len(temp)
rw = Right(temp, l - 3)
ListBox4.AddItem (Worksheets("PC Analysis").Cells(rw, 10).Value)
ListBox4.List(ListBox4.ListCount - 1, 1) = _
Worksheets("PC Analysis").Cells(rw, 9).Value
ListBox4.List(ListBox4.ListCount - 1, 2) = _
Worksheets("PC Analysis").Cells(rw, 11).Value
ListBox4.List(ListBox4.ListCount - 1, 3) = _
Worksheets("PC Analysis").Cells(rw, 12).Value
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.address <> firstaddress
End If
End With
End Sub

mythought is to copy the contents of listbox4 into an array - sort it, based
on column selected - ie button above columns clear lisbox4 and repopulate it
with the sorted array. I think this would work but its pushing me.

Thanks for your thoughts

 

[Tom Ogilvy]

Yes, that should work.

I have posted code in the past to sort a multicolumn 2D array on a selected
column. Search in Google Groups for this news group, and Ogilvy as author.