Sorting Within ListBox (Office 2003/2007)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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!

Reply | Quote

Here is a working version of your 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

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote