+ Reply to Thread
Results 1 to 31 of 31

Excel VBA: Multi-Column listbox sorting

  1. #1
    Registered User
    Join Date
    12-06-2017
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    43

    Excel VBA: Multi-Column listbox sorting

    Hello,

    The problem is the following:
    I have a listbox in a userform, which I populate from an excel sheet through array. The problem is that in the sheet there are prices which
    when I put into the listbox automatically converted to string. (So the numbers shown as string). Becouse of this I cannot sort about price
    becouse its not number but string.

    This is how I populate the listbox:
    Please Login or Register  to view this content.
    I try to sort with this code:
    Please Login or Register  to view this content.
    The sort function is working "great" but becouse of the strings its not sorted by value its sorted by abc
    1045
    2112
    2567
    299123
    320
    345000
    391

    Has anyone got any ideas?

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    Convert them to numbers when sorting?

  3. #3
    Registered User
    Join Date
    12-06-2017
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    43

    Re: Excel VBA: Multi-Column listbox sorting

    How can i do that? I tried a few method but nothing is working

  4. #4
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    Please Login or Register  to view this content.
    What have you tried?

  5. #5
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Excel VBA: Multi-Column listbox sorting

    Try using CInt/CLng or CDbl to convert string to numeric.

    For sorting, I usually use ArrayList. A method I learned from jindon's post in Orgzid.
    https://www.ozgrid.com/forum/forum/h...y-key?t=181769

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    @CK76 That may still not sort correctly though, and would only be any good fora single dimension array, this is mult-dimensional

  7. #7
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Excel VBA: Multi-Column listbox sorting

    Oh, yes. It would still need to be converted to numeric that's for sure

    To sort multi-dimension array, you can just use dictionary object in conjunction with ListArray. Though a little bit more complex.

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    You could, but it gets very complicated, it's easier just to sort the table. TBH it's probably easier to sort on the sheet and repopulate the listbox

  9. #9
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Excel VBA: Multi-Column listbox sorting

    Yes, using sheet is probably easiest to code, and usually fast enough for most application.

    Just as sample, here's workbook I did for another site, for random sampling.
    Which uses ListArray and dictionary to sort random number, item pair for sampling purpose.
    Attached Files Attached Files
    ?Progress isn't made by early risers. It's made by lazy men trying to find easier ways to do something.?
    ― Robert A. Heinlein

  10. #10
    Registered User
    Join Date
    12-06-2017
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    43

    Re: Excel VBA: Multi-Column listbox sorting

    I already tried sorting the table on the sheet. The sam problems occur, cannot convert the strings to number..

    Please Login or Register  to view this content.

  11. #11
    Forum Expert CK76's Avatar
    Join Date
    06-16-2015
    Location
    ONT, Canada
    MS-Off Ver
    Office 365 ProPlus
    Posts
    5,855

    Re: Excel VBA: Multi-Column listbox sorting

    Please upload sample workbook (just the part related to listbox), with enough sample data.
    It would be much easier for us to test solution.

  12. #12
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    Here's a working sample based on this and your other thread. Since I know you have a lot of entries, I've tweaked the sorting routine - I reckon for this many though you'd be better sorting on the sheet with a column converted to values.

    I've included 30,000 records since I know you're working with a large data set. Click the labels above the listbox to sort by that field.

    Let's hope you reply to this attachment, you ignored my last one.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    12-06-2017
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    43

    Re: Excel VBA: Multi-Column listbox sorting

    Hello Kyle123,

    Sorry for the late reply, I was away.
    I downloaded your sample, but when I try to sort its populates the listbox with the same line several times. Why is this?
    Excel Kyle.jpg

  14. #14
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    Because to replicate your dataset (30,000) I copied and pasted the same data 30 times

  15. #15
    Registered User
    Join Date
    12-06-2017
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    43

    Re: Excel VBA: Multi-Column listbox sorting

    Hi,

    I just cant put your code into mine... Always gives alarms.
    The first is this. What is the problem?
    Kyle2.jpg

  16. #16
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    You need to add the enum at the top of the module

  17. #17
    Registered User
    Join Date
    12-06-2017
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    43

    Re: Excel VBA: Multi-Column listbox sorting

    Oh my blindness
    Thanks.

    Now it works, but the problem is the same as before - the prices are sorted as text (in ABC order) and not about value. It doesnt mind if I set the Sort function to Text or Numeric its the same.

  18. #18
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    The code works fine, if it isn't working in your workbook, then you need to post an example of it not working.

  19. #19
    Registered User
    Join Date
    12-06-2017
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    43

    Re: Excel VBA: Multi-Column listbox sorting

    The problem is still what is was at the opening post. The listbox is populated from a sheet where are numbers (in correct format), but in listbox is automatically converted to string, and cant convert it back.

  20. #20
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    No, they aren't as per my example. In the workbook I posted, the numbers are strings, yet are sorted properly. The issue is therefore in your implementation, you need to post a sample that isn't working for us to be able to help you.

  21. #21
    Registered User
    Join Date
    12-06-2017
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    43

    Re: Excel VBA: Multi-Column listbox sorting

    Im trying to figure out how to send a sample without the sensitive data...

  22. #22
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    Delete the sensitive bits. Or just create a new workbook with dummy data

  23. #23
    Registered User
    Join Date
    12-06-2017
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    43

    Re: Excel VBA: Multi-Column listbox sorting

    Okay I found the source of the problem. When I populate the data I'm changing the format to show the currency name. If I take out this lines, than the sorting will be correct, but the currency is not shown. Do you have idea how to change these lines to show me currency and the sorting also works?

    Please Login or Register  to view this content.

  24. #24
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    As part of the sorting, parse the number, sort on that and then reformat

  25. #25
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    Or stick an extra column in the list box with just the number, set the width to 0 and use that column to sort on when clicking the column header of the formatted version

  26. #26
    Registered User
    Join Date
    12-06-2017
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    43

    Re: Excel VBA: Multi-Column listbox sorting

    I just put that 3 lines back to order and now working correctly with this way too.... strange....

    How about the allignment. Is there a way to allign right a column in a listbox? (So the prices will look much better).

  27. #27
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    Not easily, you could try something like this though https://wellsr.com/vba/2017/excel/al...rform-listbox/

  28. #28
    Registered User
    Join Date
    12-06-2017
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    43

    Re: Excel VBA: Multi-Column listbox sorting

    Okay! Thank you for all your help!

  29. #29
    Registered User
    Join Date
    12-06-2017
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    43

    Re: Excel VBA: Multi-Column listbox sorting

    Hi Kyle,

    Can you help me a little more? Is itt possible to alter the filtering code to work like "filter the filtered data". I mean if I have 2 or 3 textbox which filtering different columns, and I filtered one (like product name), than the second filter only filters this already filtered data?

  30. #30
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    Yes, how are you currently filtering? Yuo're going to need to attach a workbook now, things are getting a bit too in depth for generic answers

  31. #31
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,238

    Re: Excel VBA: Multi-Column listbox sorting

    Do you mean something like this?
    Please Login or Register  to view this content.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Find as you type in listbox control - solution for multi-column listbox - vba dictionary
    By wazimu13 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-05-2018, 03:42 AM
  2. [SOLVED] Populating Multi-Column Listbox with Conditions from Another Listbox
    By ykobure in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-05-2015, 02:28 AM
  3. Multi column listbox...
    By mvk1979 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-22-2013, 06:36 AM
  4. sorting multi column listbox
    By ali84pk in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 11-11-2011, 02:18 PM
  5. Sorting a multi column list in Excel
    By jonsidneyb in forum Excel General
    Replies: 4
    Last Post: 01-06-2011, 08:55 AM
  6. How to use multi-column ListBox?
    By Treacle in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-16-2010, 05:18 AM
  7. Multi Column ListBox
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 01-12-2010, 02:39 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1