MobileMobile | Continue

Excel Bar

Excel Bar

Your excel questions will be responsed by our excel experts within 24hrs.Our service is free.

 Forgot Pass?
 Register Now
Find
Hot Search: Vlookup Match VBA
12Next
Return Post new threads
View: 592|Reply: 19

Sort for multicolumn listbox in userform (5-columns) [SOLVE

[Copy link]

1

Topics

5

Posts

13

Integral

Member

Rank: 2

Integral
13
Post on 2-11-2017 08:01:48 | All posts |Read mode
Hi all,
I have a multicolumn listbox in a userform that has 5 columns.  As my dataset is growing, I need to allow users to sort the columns using command buttons placed above each column.  I have found examples of how to sort 1 or 2 columns, but cannot find anything for 5 columns.
The following is what I have used for a 2-column sort, and am curious if it can be modified to be used for a 5-colum sort.

   
    Dim i As Long
     Dim j As Long
     Dim sTemp As String
     Dim sTemp2 As String
     Dim LbList As Variant
     
     'Store the list in an array for sorting
    LbList = Me.ListBox1.List
     
     'Bubble sort the array on the first value
    For i = LBound(LbList, 1) To UBound(LbList, 1) - 1
         For j = i + 1 To UBound(LbList, 1)
             If LbList(i, 0)  LbList(j, 0) Then
                 'Swap the first value
                sTemp = LbList(i, 0)
                 LbList(i, 0) = LbList(j, 0)
                 LbList(j, 0) = sTemp
                 
                 'Swap the second value
                sTemp2 = LbList(i, 1)
                 LbList(i, 1) = LbList(j, 1)
                 LbList(j, 1) = sTemp2
             End If
         Next j
     Next i
     
     'Remove the contents of the listbox
    Me.ListBox1.Clear
     
     'Repopulate with the sorted list
    Me.ListBox1.List = LbList




Reply

Props Report

1

Topics

5

Posts

13

Integral

Member

Rank: 2

Integral
13
 Author| Post on 2-11-2017 08:13:25 | All posts



This will do it for any number of columns.
Private Sub m_SortListbox(OnColumn As Long)
    Dim vntData As Variant
    Dim vntTempItem As Variant
    Dim lngOuterIndex As Long
    Dim lngInnerIndex As Long
    Dim lngSubItemIndex As Long
   
    'Store the list in an array for sorting
    vntData = Me.ListBox1.List
     
     'Bubble sort the array on the first value
    For lngOuterIndex = LBound(vntData, 1) To UBound(vntData, 1) - 1
         For lngInnerIndex = lngOuterIndex + 1 To UBound(vntData, 1)
             If vntData(lngOuterIndex, OnColumn)  vntData(lngInnerIndex, OnColumn) Then
                'Swap values
                For lngSubItemIndex = 0 To ListBox1.ColumnCount - 1
                    vntTempItem = vntData(lngOuterIndex, lngSubItemIndex)
                    vntData(lngOuterIndex, lngSubItemIndex) = vntData(lngInnerIndex, lngSubItemIndex)
                    vntData(lngInnerIndex, lngSubItemIndex) = vntTempItem
                Next
             End If
         Next lngInnerIndex
     Next lngOuterIndex
     
     'Remove the contents of the listbox
    Me.ListBox1.Clear
     'Repopulate with the sorted list
    Me.ListBox1.List = vntData
End Sub











Reply Support Opposition

Props Report

1

Topics

5

Posts

13

Integral

Member

Rank: 2

Integral
13
 Author| Post on 2-11-2017 08:39:12 | All posts



Thank you, Andy.  I appreciate your response.
Does this now allow me to specify which column to sort by?  At first glance, to my amateur eyes, it appears not.
The other part of the challenge I face is the ability to specify the column to be sorted, and have a command button above each column to allow users to sort by those columns, respectively.
-Dave




Reply Support Opposition

Props Report

0

Topics

3

Posts

8

Integral

Newbie

Rank: 1

Integral
8
Post on 2-11-2017 10:01:59 | All posts



Yes it does. That is what the SortOn argument in the subs signature is.
This example will sort the column you double click.








  • 964420.xlsm
    (23.6 KB, 225 views)
    Download













  • Reply Support Opposition

    Props Report

    0

    Topics

    2

    Posts

    6

    Integral

    Newbie

    Rank: 1

    Integral
    6
    Post on 2-11-2017 10:40:10 | All posts



    Wow, Impressive!  Thank you so much for the file with a working sample.  Implementing this will with my application will be fun.




    Reply Support Opposition

    Props Report

    14

    Topics

    699

    Posts

    1588

    Integral

    King

    Rank: 6Rank: 6

    Integral
    1588
    Post on 2-11-2017 10:52:52 | All posts



    That was Great! Thank you so much!




    Reply Support Opposition

    Props Report

    2

    Topics

    10

    Posts

    28

    Integral

    Member

    Rank: 2

    Integral
    28
    Post on 2-11-2017 11:15:39 | All posts



    Thank you for this code. This is really great.
    But I'm actually looking for a similar code to use in a listbox in Word in which I've loaded an Excel database.
    Can you help me out here, please?
    Thanks
    Kem




    Reply Support Opposition

    Props Report

    2

    Topics

    10

    Posts

    28

    Integral

    Member

    Rank: 2

    Integral
    28
    Post on 2-11-2017 11:42:22 | All posts



    Kem60,
    Unfortunately your post does not comply with Rule 2 of our Forum
    RULES
    . Do not post a question in the thread of another member -- start your own thread.
    If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
    Old threads are often only monitored by the original participants.  New threads not only open you up to all possible participants again, they typically get faster response, too.











    Reply Support Opposition

    Props Report

    2

    Topics

    7

    Posts

    26

    Integral

    Member

    Rank: 2

    Integral
    26
    Post on 2-11-2017 12:44:14 | All posts



    Hello Andy,
    I was looking for a code to sort a multicolumn list box and i came across your double click feature which is really impressive!
    So i implemented my excel case into your double click idea but i noticed that the column that has dates doesn't get sorted properly.
    I uploaded a sample of my work.
    Please note that my userform-initialize to populate the list box is constructed differently than yours.
    Thanks!








  • Sort listbox.xlsm
    (25.9 KB, 8 views)
    Download








  • Reply Support Opposition

    Props Report

    2

    Topics

    10

    Posts

    28

    Integral

    Member

    Rank: 2

    Integral
    28
    Post on 2-11-2017 13:08:27 | All posts



    I think the issue lies when i loop through rows, not just the dates.
    Just wanted to clarify.




    Reply Support Opposition

    Props Report

    12Next
    Return Post new threads

    Points policy of this forum

    Archiver|Mobile|Small dark house|Contact us|Excel Bar

    GMT-5, 9-21-2017 14:35 , Processed in 0.184256 second(s), 20 queries .

    Powered by Discuz! X3

    © 2001-2013 Comsenz Inc.

    !fastreply! Top !return_list!