Solved

# Sorting Listbox on userform by 2 columns vba code Update needed

Posted on 2011-10-31
Medium Priority
423 Views
excel 2003

I have the following code working fine on a 1 Column Sort.

What I need:
I need to sort the grid using the following code by 2 Column ?

To Call:

Run "SortListBox", ListBox1, 0, 1, 1

Thanks
fordraiders

``````Public 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

If oLb.ListCount = 0 Then
Exit Sub

End If
'Put the items in a variant array
vaItems = oLb.List
' no records found exit here

'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
``````
0
Question by:Fordraiders
• 3
• 3

LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 37056736
You could simply concatenate before you compare:

``````If vaItems(i, sCol) & "|" & vaItems(i, sCol2) > vaItems(j, sCol) & "|" & vaItems(j, sCol2) Then
``````
0

LVL 3

Author Closing Comment

ID: 37065992
Thanks
0

LVL 3

Author Comment

ID: 37131689
rorya, Sorry to bother you on a closed question..but I have a question...

How to call this correctly...
I'm assuming I would have to redu the variables to include a second column sort ?
Public Sub SortListBox(oLb As MSForms.ListBox, sCol As Integer, sType As Integer, sDir As Integer)

Run "SortListBox", ListBox1, 0, 1, 1

to

Public Sub SortListBox(oLb As MSForms.ListBox, sCol As Integer, sCol2 As Integer, sType As Integer, sDir As Integer)

Run "SortListBox", ListBox1, 0, 3, 1 , 1

?? I can reopen the question if you like ?

Thanks
fordraiders

0

LVL 85

Expert Comment

ID: 37131719
That's correct.
0

LVL 3

Author Comment

ID: 37131827
ok , so the original code below does not have to altered at all ? to allow for sCol2

'Put the items in a variant array
vaItems = oLb.List
' no records found exit here

'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

Thanks again

0

LVL 85

Expert Comment

ID: 37132101
It seems neither of us tested my suggestion! :)

``````Public Sub SortListBoxTwoCols(oLb As MSForms.ListBox, sCol As Integer, sCol2 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

If oLb.ListCount = 0 Then
Exit Sub

End If
If sCol2 = sCol Then
SortListBoxOneCol oLb, sCol, sType, sDir
Else

'Put the items in a variant array
vaItems = oLb.List
' no records found exit here

'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) Or (vaItems(i, sCol) = vaItems(j, sCol) And vaItems(i, sCol2) > vaItems(j, sCol2)) 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) Or (vaItems(i, sCol) = vaItems(j, sCol) And vaItems(i, sCol2) < vaItems(j, sCol2)) 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)) Or (CInt(vaItems(i, sCol)) = CInt(vaItems(j, sCol)) And CInt(vaItems(i, sCol2)) > CInt(vaItems(j, sCol2))) 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)) Or (CInt(vaItems(i, sCol)) = CInt(vaItems(j, sCol)) And CInt(vaItems(i, sCol2)) < CInt(vaItems(j, sCol2))) 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 If
End Sub
Public Sub SortListBoxOneCol(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

If oLb.ListCount = 0 Then
Exit Sub

End If   'Put the items in a variant array
vaItems = oLb.List
' no records found exit here

'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
``````

Note that this requires both columns to be the same data type.
0

## Featured Post

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabilâ€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
###### Suggested Courses
Course of the Month16 days, 8 hours left to enroll