sorting a listbox in VBA

I have a listbox with values. Now I need to sort that listbox, BUT NOT BY ALPHABETICALLY.

You see the values in a listbox are from a table.

----------------开发者_如何学JAVA--------
| name | order |  size |
========================
 value1   4
 value2   3
 value3   1 
 value4   2

I hope I made myself clear. So the list box has the items "value1, value2, value3, value4". I want it to sort by the order declared in the table. The list box has nothing to do with the table and is not "data bound" to it. The values come from somewhere else.

Tech used: VBA, Access 2007

It sounds like the Row Source Type for your list box is "Value List". Since the values come from a table, change Row Source Type to "Table/Query", then use a query for Row Source:

SELECT [name], [order]
FROM YourTable
ORDER BY [order];

You don't have to display [order] in the list box ... set its column width to zero.

Your list box control does not have to be bound to a data source field for this approach to work. If the list box is unbound, the user selection will not be stored, but the list box will still allow users to make selections from the list box.

Notice I enclosed [name] and [order] in square brackets because both are reserved words. See Problem names and reserved words in Access

Dim First           As Integer
Dim Last            As Integer
Dim a               As Integer
Dim b               As Integer
Dim Temp            As String
Dim test()          As String

First = LBound(test)
Last = UBound(test)
For a = First To Last - 1
    For b = a + 1 To Last
        If test(a) > test(b) Then
            Temp = test(b)
            test(b) = test(a)
            test(a) = Temp
        End If
    Next b
Next a

For a = 1 To UBound(test)

    lst_email.AddItem test(a) 'lst_email is a listbox

Next