Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Christchurch, New Zealand
    Posts
    250
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Sorting Within ListBox (Office 2003/2007)

    I have a mental block when it comes to working with arrays, and I think this is where my problem lies with this exercise. I have a user form containing a listbox which has items added to it at runtime. The user can also add or delete items from the listbox. I want to be able to sort this listbox alphabetically, and then eventually I will take that information and write it back to a text file.

    The problem I'm having is with the sort.

    I've read previous posts about sorting and followed links, but as my understanding of arrays is limited, i can't debug this.

    I've attached a document with the form that I have now, and it gets stuck on the sorting. Depending on how I change the code I get errors such as "type mismatch", "out of subscript range", etc. I've been going around in circles with this one for hours, so any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Sorting Within ListBox (Office 2003/2007)

    You do not actually need to use SortArray. You can juggle the items directly inside the list box. This old thread contains code based on two basic algorithms: bubble sort and quick sort: Sorting list boxes in VBA (Word 2000 VBA). You need to check all the posts toward the end because there are some corrections and caveats.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting Within ListBox (Office 2003/2007)

    Here is a working version of your code:
    <code>
    Private Sub btnSort_Click()
    Dim icnt As Integer
    Dim ss() As String
    Dim i As Integer

    icnt = ListBox1.ListCount
    ReDim ss(icnt - 1)

    For i = 0 To icnt - 1
    ss(i) = ListBox1.List(i)
    Next i

    WordBasic.SortArray ss

    For i = 0 To icnt - 1
    ListBox1.List(i) = ss(i)
    Next i
    End Sub
    </code>
    You can't declare a constant using a calculated expression involving variables.
    You can't declare an array using a variable upper bound, you must first declare a dynamic array (no bounds specified), then use ReDim to specify the upper bound.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Christchurch, New Zealand
    Posts
    250
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting Within ListBox (Office 2003/2007)

    Thank you Hans. This works perfectly with the sample form, but when I put into my actual form, which is a multipage form, I get a subscript out of range error. Firstly I thought it was because I was initially populating the listbox with items from a text file, but even when I used a simple additem to populate the listbox, I get the error when I try to sort the list. I've attached the actual form here if you can tell me why there is a difference.

    I'm still looking at the posts that Jeff recommended and trying to make sense out of them.
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting Within ListBox (Office 2003/2007)

    This is because you have a line

    Option Base 1

    near the top of the module. This makes array indexes start at 1. However, the List of a list box starts at 0, so there is a discrepancy. If you delete or comment out the line, the code will work OK.

    You may have to adjust other code, I haven't checked that.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Christchurch, New Zealand
    Posts
    250
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting Within ListBox (Office 2003/2007)

    Thank you. I had just found that, and now have to try and work out why I had it there in the first place! It was possibly just a legacy as part of this form was used on another project.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Christchurch, New Zealand
    Posts
    250
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting Within ListBox (Office 2003/2007)

    Thanks Jeff. Your second reply to my post (which for some reason I can't see online) that VBA arrays are zero-based lead me to look again at arrays and in particular "base". I had an Option Base 1 setting (probably left over from when I used this form slightly differently for another project) and once this was removed everything worked. Now I just have to check that removing it didn't break something else!

Posting Permissions

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