Sort a value list?

Posted on 2003-03-06
Medium Priority
Last Modified: 2008-02-01
I have a value list that must change dynamically on the form as the user selects various choices.  The additem and removeitem work fine, but keeping this list sorted as items are added is the problem.

Here is the procedure I wrote..

Public Sub SortList(ByRef aList As ListBox)
    Dim count As Integer, J As Integer, temp As String
    Dim NoExchangeInPass As Boolean
    count = aList.ListCount
    NoExchangeInPass = False
    Do Until (NoExchangeInPass)
        NoExchangeInPass = True
        For J = 1 To (count - 1)
            If (aList.ItemData(J) > aList.ItemData(J + 1)) Then
                temp = aList.ItemData(J)
                aList.ItemData(J) = aList.ItemData(J + 1)
                aList.ItemData(J + 1) = temp
                NoExchangeInPass = False
            End If
        Next J
        count = count - 1
End Sub

I get an "Object Required" message when the program reaches "aList.ItemData(J) = aList.ItemData(J+1)".

I assume its because the .itemdata method is not an assignment method.

Any ideas how I can do this?
Question by:Plamodo
  • 2
  • 2
LVL 41

Expert Comment

ID: 8081906
That's right - you should, however, be able to split the row source of the list box into an array using the Split function. You can then remove all the items from the list, sort the array and re-assign it back to the list box using the Join function to turn it back into a value list and assigning it directly to the list box's RowSource property.

Author Comment

ID: 8087708
Great!  Would you mind jotting down how to perform this split function and join function - I've never heard of them before.
LVL 41

Accepted Solution

shanesuebsahakarn earned 200 total points
ID: 8087986
I assume you're using A2K2 since you're using AddItem - what you can do is this:

Dim varItemArray As Variant

varItemArray = Split(Me!aList.RowSource,";")

This will give you an array containing each item. Once you've sorted them, you can use:
Me!aList.RowSource = Join(varItemArray,";")


Author Comment

ID: 8093084
Worked like a charm.. Thanks for the tip!  My only other idea was to manually stick every element into an array as I did a removeitem, sort the array and then loop thru and additem everything back in .. your method is 100 times more efficient.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question