Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511

    Question Unanswered: Can items in a list box be re-sorted?

    In Excel 2000, I have 1,200 product codes and names in a list box which is populated with products from 2 columns on a hidden worksheet (also used as a lookup table for one of the worksheets), using ADDITEM in a FOR loop. The list on the worksheet is sorted alphabetically by product name.

    Example data in listbox:
    2222 ABC DEPOSIT
    1234 ACC DAILY CASH
    ...
    1000 ZERO BALANCE ACCT

    I want to add the option on the listbox form to sort alternately by each item. I know I can do this by sorting the list on the worksheet and re-filling the list box, but would like to know if there is a way to take what is in the listbox and sort it, either by code or name. I have seen this (a sorted listbox) in VISUAL BASIC, but not with VBA. If there is such a thing as listbox sorting, I can work out the logic of parsing the product code and product name from the listbox and perform alternate sorting with a command button.

    Thanks.
    Jerry

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Here is a solution from Andrew Poulsom

    Code:
    Private Sub UserForm_Initialize() 
        Dim i As Long 
        Dim j As Long 
        Dim Temp As Variant 
        With ListBox1 
            .AddItem "Z" 
            .AddItem "Y" 
            .AddItem "X" 
            .AddItem "C" 
            .AddItem "B" 
            .AddItem "A" 
            For i = 0 To .ListCount - 2 
                For j = i + 1 To .ListCount - 1 
                    If .List(i) > .List(j) Then 
                        Temp = .List(j) 
                        .List(j) = .List(i) 
                        .List(i) = Temp 
                    End If 
                Next j 
            Next i 
        End With 
    End Sub
    HTH
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Your solution:

    Private Sub UserForm_Initialize()
    Dim i As Long
    Dim j As Long
    Dim Temp As Variant
    With ListBox1
    .AddItem "Z"
    .AddItem "Y"
    .AddItem "X"
    .AddItem "C"
    .AddItem "B"
    .AddItem "A"
    For i = 0 To .ListCount - 2
    For j = i + 1 To .ListCount - 1
    If .List(i) > .List(j) Then
    Temp = .List(j)
    .List(j) = .List(i)
    .List(i) = Temp
    End If
    Next j
    Next i
    End With
    End Sub
    ----------------------------------------------------------------
    Thanks for the above Excel list box sorting solution. I tested it on my office PC (2.3 Mhz, 512 MB RAM) and here are the test results:

    1,200 items - 55 seconds
    600 items - 15 seconds
    300 items - 5 seconds
    200 items - 2 seconds
    100 items - less than 1 second

    As you can see, the solution is not practical for sorting 1,200 list box items, but the solution would be perfect for 200 or less items.

    The solution I am using, which takes less than 1 second to re-fill the listbox, is to store both versions of the 1,200 item sorted list in a hidden worksheet. The sorting command button caption initially reads: "Sort by Code", and when clicked with this caption, the list box is cleared and filled in with the code-sorted list; the button caption is changed to "Sort by Name". The button caption is tested to determine what the next sort will be, and alternately changed between "Sort by Name" and "Sort by Code".

    My name-sorted list doubles as a look up table, and that is why I did not choose to sort it each time by name or code to fill the list box.

    Jerry

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    Yep, that's the direction I would have gone. But I thought you wanted to do it directly in the list.


    Good to hear that your solution does what you want - and thanks for providing feedback to this list. It helps - all of us, who are learning or providing options.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •