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,
--
Martin

[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.

--
Regards,
Tom Ogilvy

"Martin" <[email protected]> wrote in message
news:[email protected]...
> 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,
> --
> Martin

[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
--
Martin


"Tom Ogilvy" wrote:

> 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.
>
> --
> Regards,
> Tom Ogilvy
>
> "Martin" <[email protected]> wrote in message
> news:[email protected]...
> > 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,
> > --
> > Martin
>
>
>

[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.

--
Regards,
Tom Ogilvy

"Martin" <[email protected]> wrote in message
news:[email protected]...
> 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
> --
> Martin
>
>
> "Tom Ogilvy" wrote:
>
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Martin" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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,
> > > --
> > > Martin
> >
> >
> >