18 thoughts on “Sorting ListBoxes”

1. How can I in Excel 2003 have ONLY SELECTION MADE in an expanded multi-select list box print or mail merge to another document.

2. The piece of Code was cool.. it worked well..

3. How do I call this? I have a listbox importing a list of groups from the domain and I want to sort it alphabetically after it has got all the groups

4. Nice function thanks for sharing.
   It works great when there are strings in the ListBox, but not when its filled with numbers..

   I have my listBox filled with these numbers:
   41,2,4,1,7,11,75

   After the sorting they are sorted like characters:
   1,11,2,4,41,7,75

   But what if I would like them to be sorted like numbers?
   1,2,4,7,11,41,75

   Any ideas how I can do this?

5. I can’t figure out how to pass my listbox to the routine – it always comes back at me and says ‘object required’. Am I doing something wrong? I enter SortListBox(lbxname) and it doesn’t seem to like my arguments. Suggestions?

6. Aaron, make sure it’s – “MSForms.ListBox”. It took me 2hrs to fix it.

7. For the benefit of Thomas or anyone in a similar situation:

   Note that Listboxes treat their items as character strings, so the comparison as written above:
   vaItems(i, 0) > vaItems(j, 0)
   is a string comparison. Even if you put in numbers when you call Listbox.Add(), the Listbox will convert them to strings for storing internally, and so will give you back an array of strings when you call Listbox.List() (and that array is what is getting sorted).

   To sort the data in numerical order, you need to make numeric comparisons, so you need to convert the items in the array to numbers (such as integers) before comparing them, something like:
   CInt(vaItems(i, 0)) > CInt(vaItems(j, 0))

   However, you should choose the appropriate conversion function for your data. If you are sure your data are relatively small integers (less than 2 billion), CInt() will suffice, but very large numbers will require CLng(), and numbers that have decimal components will require CDec() (for exact decimal comparison) or CDbl() (for approximate decimal comparison).

   After the comparison, it’s not necessary to treat the data as numbers anymore, so the rest of the code remains the same.

8. To add to this just in case anyone is interested there is a way to get round the ridiculous 11 columns nonsense. Use arrays ! setup Listbox data in an array and then the list box will behave it self. To be honest I cannot believe that these list boxes have been dragged kicking and screaming from excel 97 through to 2003 with little or no changes.

9. Please moderate this comment instead…

   I had the same problem as Aaron- “object required.” I was confused about Julian’s comment- make sure WHAT is MSForms.ListBox? I copy and pasted the code and assume he was talking about the function argument.

   My problem was from using parenthesis to call the function:

   WRONG: SortListBox(MyForm.MyListBox)
   RIGHT: SortListBox MyForm.MyListBox

   Apparently this is a VBA quirk that I have not encountered in the past.

   Thanks for the script. Even with the time spent troubleshooting I’m sure it saved me an hour or so.

10. I tried the code as-is (copied to my 2007 workbook VBA) and couldn’t figure out what was wrong with it until I noticed that the comments in the code were causing errors. I had noticed that they were highlighted (error-red) and thought the highlight was due to displaying the comments. After I reversed the quote marks the code worked just fine.

    I’m pointing this out in case somebody else overlooked the comments like I did.

11. Why using

     ‘Clear the listbox
    oLb.Clear
       
     ‘Add the sorted array back to the listbox
    For i = LBound(vaItems, 1) To UBound(vaItems, 1)
       oLb.AddItem vaItems(i, 0)
     Next i

    Instead of

      oLb.Clear
      oLb.List=vaItems

12. My sorting solution

    Sub A_sortlistbox(oLb As ComboBox)
      With Sheets(1).Range(Cells(1, 100), Cells(oLb.ListCount, 100 + oLb.ColumnCount))
        .Value = oLb.List
        .Sort .Cells(1, 1)
        sq = .Value
        oLb.List = sq
      End With
    End Sub

13. Thx! Good code, worked for me on the first try!

14. Great, helpful code!
    I used it for my combo boxes, at first I got a type mismatc error #13, changing “MSForms.ListBox” to “MSForms.ComboBox” solved the problem.

    Also had a problem with the “-1” at the end of line “For i = LBound(vaItems, 1) To UBound(vaItems, 1) – 1”, remove the -1 and the code is perfect.

    Thank you very much!

15. I’m having the same “Use-Defined Type not defined” message, though I’m using Access 2007.

    My line looks like this: Sub lbl_network_id_Click(oLb As MSForms.ListBox)

    I’ve tried removing the parenthesis as explained by another poster…

    Also how does the array know which column to sort by? I have a list with 4 columns.

    Any help on the above would be appreciated!

16. If you’re getting User Defined Type not defined in Excel, you need to set a reference to Microsoft Forms 2.0 Object Library – although this reference gets set automatically when you insert a userform into your project and if you haven’t done that yet, then where is your code living?. But Access uses a different forms engine. Your best bet is to change the declaration to

    Sub lbl_network_id_Click(oLB as ListBox)

    Remove the MSForms part and let access resolve which library ListBox should come from.

    The procedure above sorts on the first column, which is column zero.

    If vaItems(i, 0) > vaItems(j, 0) Then

    The zero in the second position of the array reference means the first column. Change it to a 1 for the 2nd column, etc.

17. FYI, Access does also use the MSForms engine. It doesn’t show up as a default choice in the VBE’s Insert menu, but you can add it as a choice. I’ve used it in conjunction with “regular” Access forms.

18. Pingback: Using a user form to delete specified row

Posting code? Use <pre> tags for VBA and <code> tags for inline.