Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Sort ListBox Or Combobox List Items

  1. #1
    Join Date
    20th February 2008
    Posts
    31

    Sort ListBox Or Combobox List Items

    The code for sorting a multi-column listbox in this thread was really good, and I've used it a lot in an application I'm building.

    What I'm hoping to do is reload a listbox in such a way that the items appear in the same order they were previously in. I'll describe two scenarios:
    1. User edits a record
    - user sorts listbox
    - user selects a record
    - user edits record
    - listbox reloads, unsorted

    2. User adds a record
    - user sorts a listbox and sees a record is missing
    - user adds the missing record
    - listbox reloads, unsorted

    ideally the last step for each would be "listbox reloads, sorted" and the user would go on down the list. the tricky part i think is when rows are deleted or added.

    i'm starting on a solution, but if there's some existing code that will do this i'd appreciate if someone could point me to it (because, for example, the listbox sorting code i referred to above anticipated things i did not).

    edit: my first thought was just to sort the listbox again by the same column when the "reload" subroutine runs, but it seems this does not work. if i sort ascending by first name, "John Doe", "John Smith", and "John Johnson" will not appear in the same order each time. (i thought i could write the "sort by last name" sub to run the "sort by first name" sub first, but then things get recursive and my head just starts to hurt.)

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

  3. #3
    Join Date
    20th February 2008
    Posts
    31

    Re: Sort ListBox Or Combobox List Items

    thanks Dave. as i mentioned, the code from that first link is exactly what i'm using. i'm not sure how the second link is relevant.. could you explain?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Sort ListBox Or Combobox List Items

    as i mentioned, the code from that first link is exactly what i'm using
    No, you didn't.

    i'm not sure how the second link is relevant.. could you explain?
    It sorts listbox items, so it's very "relevant".

  5. #5
    Join Date
    20th February 2008
    Posts
    31

    Re: Sort ListBox Or Combobox List Items

    sorry if you missed it - here is the part where i quoted the code you reposted:
    Quote Originally Posted by terracotta
    The code for sorting a multi-column listbox in this thread was really good, and I've used it a lot in an application I'm building.
    and yes it is relevant, and it is the code i'm using, but here is my problem with the code:

    Quote Originally Posted by terracotta
    if i sort ascending by first name, "John Doe", "John Smith", and "John Johnson" will not appear in the same order each time. (i thought i could write the "sort by last name" sub to run the "sort by first name" sub first, but then things get recursive and my head just starts to hurt.)
    also, if you could please change the title back to its original title i would greatly appreciate it, since this isn't so much about sorting lists (a topic that's been covered a billion times i'm sure) as it is about sorting them consistently.

    sorry if my writing wasn't clear, and thanks for your patience. i'm sure that as an administrator you are very busy, so if you could recommend another place i could ask this question that would be useful as well.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Sort ListBox Or Combobox List Items

    sorry if you missed it
    I haven't missed anything. Slow down, calm down and READ my post and you will see I linked to another thread. Different link text and different thread.

    and yes it is relevant, and it is the code i'm using, but here is my problem with the code:
    You asked how the SECOND link is relevant. To which I answered and it shows you the answer, granted NOT pan ready, but an "Above Average" user can work it out, or at LEAST try.


    also, if you could please change the title back to its original title i would greatly appreciate it, since this isn't so much about sorting lists (a topic that's been covered a billion times i'm sure) as it is about sorting them consistently.
    No. You used "Reloading Listbox Contents With Items Sorted" I only took out the word "Reloading" as it's superflorous despite your assumption it isn't.


    so if you could recommend another place i could ask this question that would be useful as well.
    Google will yeild 100000's of results. IF YOU CROSS POST, YOU MUST SHOW THE LINKS ELSE YOU WILL BE BANNED.

  7. #7
    Join Date
    20th February 2008
    Posts
    31

    Re: Sort ListBox Or Combobox List Items

    Quote Originally Posted by Dave Hawley
    I haven't missed anything. Slow down, calm down and READ my post and you will see I linked to another thread. Different link text and different thread.
    isn't it the same code though?

    as i described it's sorting a multiple column listbox the same way every time that i'm having trouble with.

    for example, if you have a 2-column listbox with "first name" and "last name" columns, aplpying the SortListBox macro and sorting by first name leaves entries with the same first name sorted arbitrarily. for example, "John Doe", "John Smith" and "John Johnson" will be in a different order everytime you sort by first name ascending. what i'm looking for is a way to sort so that these names appear in the same order every time.

    thank you also for the second bit of code. unfortunately i'm probably not even an above average user so i might need some help seeing how it's relevant, because after quite a bit of time working through it i can't see how it is. i'm not ahving any trouble removing duplicates and sorting single column listboxes, if that's the impression i gave.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,717

    Re: Sort ListBox Or Combobox List Items

    isn't it the same code though?
    No idea, only YOU know what code you have.

    for example, if you have a 2-column listbox with "first name" and "last name" columns, aplpying the SortListBox macro and sorting by first name leaves entries with the same first name sorted arbitrarily. for example, "John Doe", "John Smith" and "John Johnson" will be in a different order everytime you sort by first name ascending.
    __________________
    That's why I showed you the better way, using the RowSource.

    Bottom line is, I cannot help those who refuse to open their eyes and take the advice they have asked for.

  9. #9
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,565

    Re: Sort ListBox Or Combobox List Items

    Does this help?
    VB:
    Sub test() 
        Dim a 
        a = ListBox1.List 
        Redim Preserve a(LBound(a,1) To UBound(a,1), LBound(a,2) To UBound(a,2) +1) 
        For i = LBound(a,1) To UBound(a,1) 
            a(i, UBound(a,2)) = UCase(a(i,1) & " " & a(i,2)) 
        Next 
        VSortMA a, LBound(a,1), UBound(a,1), UBound(a,2) 
        ListBox1.List = a 
    End Sub 
     
    Private Sub VSortMA(ary, LB, UB, ref) 
        Dim M As Variant, i As Long, ii As Long, iii As Long 
        i = UB : ii = LB 
        M = ary(Int((LB+UB)/2),ref) 
        Do While ii <= i 
            Do While ary(ii,ref) < M 
                ii = ii + 1 
            Loop 
            Do While ary(i,ref) > M 
                i = i - 1 
            Loop 
            If ii <= i Then 
                For iii = LBound(ary,2) To UBound(ary,2) 
                    temp = ary(ii,iii) : ary(ii,iii) = ary(i,iii) 
                    ary(i,iii) = temp 
                Next 
                ii = ii + 1 : i = i - 1 
            End If 
        Loop 
        If LB < i Then VSortMA ary, LB, i, ref 
        If ii < UB Then VSortMA ary, ii, UB, ref 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    20th February 2008
    Posts
    31

    Re: Sort ListBox Or Combobox List Items

    thanks Jindon. i applied it to a 4-column listbox where columns 2 and 3 were first name and last name respectively, and it sorts exactly the same way each time when there are no rows with the same first-last name combination (which is exactly what i was hoping for!). i don't quite know what's going on in each line of code, but at a glance, it seems to sort by column 2 and then column 3.

    the next thing i did was to let a first-last name combination be used more than once (col 2 and 3), but place a unique identifier in column 1. so two "John Doe" records, but different ID values for each - the result is that the ID values come in a different order every time. but this code is still a good start i'll see if i can generalize what you've done.


    Dave:
    the SortListBox code in the thread you posted is the same as the SortListBox code in the one i posted.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 13
    Last Post: May 13th, 2009, 10:21
  2. Sort UserForm Combobox List
    By JoePineapples in forum EXCEL HELP
    Replies: 4
    Last Post: May 15th, 2008, 19:16
  3. ListBox Retaining Old List Values/Items
    By Simon Bianchi in forum EXCEL HELP
    Replies: 3
    Last Post: September 4th, 2006, 12:01
  4. Combobox list and sort
    By Mavryk76 in forum EXCEL HELP
    Replies: 4
    Last Post: July 25th, 2006, 16:21

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
  •  
porno