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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

762 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