Announcement

Collapse
No announcement yet.

Sort ListBox Or Combobox List Items

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.)

  • #2
    Re: Reloading Listbox Contents With Items Sorted

    http://www.ozgrid.com/forum/showthread.php?t=71509

    Sort ListBox Items & Make Unique
    Sort ListBox Items & Make Unique

    Comment


    • #3
      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?

      Comment


      • #4
        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".

        Comment


        • #5
          Re: Sort ListBox Or Combobox List Items

          sorry if you missed it - here is the part where i quoted the code you reposted:
          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:

          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.

          Comment


          • #6
            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.

            Comment


            • #7
              Re: Sort ListBox Or Combobox List Items

              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.

              Comment


              • #8
                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.

                Comment


                • #9
                  Re: Sort ListBox Or Combobox List Items

                  Does this help?
                  Code:
                  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

                  Comment


                  • #10
                    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.

                    Comment


                    • #11
                      Re: Sort ListBox Or Combobox List Items

                      Can you show me the list of listbox ?

                      Note: I can only see worksheet.
                      Can not run vba code, so no userform/controls can be seen due to the security here.
                      And no zipped file pls.

                      Comment


                      • #12
                        Re: Sort ListBox Or Combobox List Items

                        i've mocked up a quick userform and took some screenshots to show what's going on:

                        http://i15.photobucket.com/albums/a3...lumnSort-2.jpg

                        as described, the list sorts differently everytime you sort by First Name ascending. i guess the tricky bit going down this path is generalizing to N columns. given that the problem in the first place was to reload/refresh the existing listbox in the same order that it last appeared in (with new/updated records), i think generalizing to N columns will be overkill.

                        edit: since photobucket shrank the image first time.
                        Auto Merged Post Until 24 Hrs Passes;

                        okay.. i'm now a little embarassed to say i've solved this because the solution pretty simple in hindsight.

                        if i wanted to sort by First Name and get the records showing up the same way each time, all i had to do was sort by the unique identifier first before sorting by First Name. so if we go by the screenshot above, i should just let the "sort by last name" macro (and every other sort macro) run the "sort by Record ID" as well.

                        in the absence of a unique identifier i could sort by _every_ other column first, in a predefined order, then by First Name. so the "sort by first name" sub would sort by ID first, then last name, then balance, then finally by first name. if i had 200 columns in my listbox and no unique identifier (good luck..), i could do this looping from i=0 to ListBox.ColumnCount-1. the effect is close to sorting by a unique identifier then by first name.
                        Last edited by terracotta; May 10th, 2008, 17:45. Reason: Auto Merged Doublepost

                        Comment


                        • #13
                          Re: Sort ListBox Or Combobox List Items

                          Did you sorted out?
                          If no, how do you want it?

                          Comment


                          • #14
                            Re: Sort ListBox Or Combobox List Items

                            yup all sorted out.

                            pun intended.

                            Comment

                            Working...
                            X