PDA

View Full Version : Sorting a multicolumn listbox



p9drm1
07-16-2018, 04:41 AM
Hello,

I have a multi-column listbox with 3 columns. I would like to sort the items in thelistbox from in ascending order (A toZ) based on the items in column one. I'm using the following code to sort the items but it is only sorting theitems in the first column. I would likefor columns two and three to be sorted as well matched up with the sort that took place incolumn one. Can this be down with amulti-column listbox with three columns?

Private Sub CommandButton1_Click()
'Sorts ListBox List
Dim i As Long
Dim j As Long
Dim temp AsVariant

With Me.ListBox1
For j = 0 ToListBox1.ListCount - 2
For i = 0To ListBox1.ListCount - 2
If.List(i) > .List(i + 1) Then
temp = .List(i)
.List(i) = .List(i + 1)
.List(i + 1) = temp
End If
Next i
Next j
End With
End Sub

regards,

Darryl R. Moore

p9drm1
07-16-2018, 07:32 AM
Found my answer:

Sub SortListBox(oLb As MSForms.ListBox, sCol As Integer, sType As Integer, sDir As Integer)
Dim vaItems As Variant
Dim i As Long, j As Long
Dim c As Integer
Dim vTemp As Variant

'Put the items in a variant array
vaItems = oLb.List

'Sort the Array Alphabetically(1)
If sType = 1 Then
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
'Sort Ascending (1)
If sDir = 1 Then
If vaItems(i, sCol) > vaItems(j, sCol) Then
For c = 0 To oLb.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

'Sort Descending (2)
ElseIf sDir = 2 Then
If vaItems(i, sCol) < vaItems(j, sCol) Then
For c = 0 To oLb.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
End If

Next j
Next i
'Sort the Array Numerically(2)
'(Substitute CInt with another conversion type (CLng, CDec, etc.) depending on type of numbers in the column)
ElseIf sType = 2 Then
For i = LBound(vaItems, 1) To UBound(vaItems, 1) - 1
For j = i + 1 To UBound(vaItems, 1)
'Sort Ascending (1)
If sDir = 1 Then
If CInt(vaItems(i, sCol)) > CInt(vaItems(j, sCol)) Then
For c = 0 To oLb.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

'Sort Descending (2)
ElseIf sDir = 2 Then
If CInt(vaItems(i, sCol)) < CInt(vaItems(j, sCol)) Then
For c = 0 To oLb.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
End If

Next j
Next i
End If

'Set the list to the array
oLb.List = vaItems
End Sub
You would run it by calling the procedure like this:
Run "SortListBox",
[ListBox Name],
[ListBox column to sort by],[Alpha(1) or Numeric(2) Sort],[Ascending(1) or Descending(2) Order]


Code:
'Sort by the 1st column in the ListBox Alphabetically in Ascending Order
Run "SortListBox", ListBox1, 0, 1, 1

'Sort by the 1st column in the ListBox Alphabetically in Descending Order
Run "SortListBox", ListBox1, 0, 1, 2

'Sort by the 2nd column in the ListBox Numerically in Ascending Order
Run "SortListBox", ListBox1, 1, 2, 1

'Sort by the 2nd column in the ListBox Numerically in Descending Order
Run "SortListBox", ListBox1, 1, 2, 2

nimesh29
02-17-2020, 03:28 PM
Darryl, where you placing your 'Run' code?

Also, does this have columns limit and how does it handle numbers greater then 9 (ie: 10, 11, 20, 21, 30)? I have being having issue with sorting double digits, I'll get; 1, 10, 11, 2, 3...


Nimesh