?
Solved

Sorting Listbox on userform by 2 columns vba code Update needed

Posted on 2011-10-31
6
Medium Priority
?
423 Views
Last Modified: 2012-06-21
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

Open in new window

0
Comment
Question by:Fordraiders
  • 3
  • 3
6 Comments
 
LVL 85

Accepted Solution

by:
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

Open in new window

0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 37065992
Thanks
0
 
LVL 3

Author Comment

by:Fordraiders
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 37131719
That's correct.
0
 
LVL 3

Author Comment

by:Fordraiders
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

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

Try this instead:
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

Open in new window


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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question