Excel VBA UserForm ListBox: Sorting Options and Code

To sort a list in an Excel VBA UserForm, sort the values before adding them to the ListBox; the standard MSForms ListBox does not provide a general-purpose Sorted property. The example below reads names from a worksheet, sorts them alphabetically without changing the worksheet, and then loads them into the control.

Why a UserForm ListBox Needs Sorting Code

A ListBox displays items in the order they are supplied. If you add entries with AddItem, the first item added appears first, the next item appears beneath it, and so on. If the control uses a worksheet range through its RowSource property, it displays the rows in the range’s existing order. Neither approach automatically rearranges entries alphabetically just because the control is a ListBox.

This is a common source of confusion because other list controls and applications may offer a sort setting. For a standard Excel VBA UserForm ListBox, the reliable approach is to prepare the data in the desired order and then load it. That preparation can happen in a VBA array, in a sorted worksheet range, or in another data source. Sorting an array is useful when the form should show an ordered view but the original data must stay untouched.

Before choosing code, decide what “sorted” means for the list. Alphabetical text order, numerical order, descending order, and sorting complete records by one field are different tasks. A method that is right for a list of names may give surprising results for numbers or rows containing several related fields.

Choose Where the Sort Should Happen

There are three practical places to sort. Each suits a different workbook design:

Sort an array in VBA: The form gets an ordered display, while the source cells remain as they were. This is a good fit for a small or medium list that is loaded when the form opens.
Sort the worksheet data: Excel’s worksheet sort can arrange a table or range before the form reads it. This changes the order of the source data, so use it only when that change is wanted or sort a separate copy.
Sort the source with a formula or query: A worksheet can provide an already ordered result for the form to read. This can suit a workbook that already uses dynamic arrays or Power Query, but it adds a dependency on that setup.

The array method in the next sections uses a simple insertion sort. It is easy to inspect and is suitable for ordinary UserForm lists. For very large datasets, sort the data using Excel’s range-sorting tools or a more scalable data workflow rather than repeatedly inserting items into an array one at a time.

Set Up the UserForm and Source Data

In this example, the worksheet is named Data, names are in column A beginning at A2, and row 1 is a header. The UserForm contains a ListBox named lstNames. In the Visual Basic Editor, add a UserForm, insert a ListBox, and set its (Name) property to lstNames. The code belongs in that UserForm’s code module, not in a worksheet cell.

On Windows, open the Visual Basic Editor with Alt+F11, then double-click the form in Project Explorer to open its code module. On Mac, the VBA editor shortcut is commonly Option+F11; some keyboards require Fn as well. Excel for the web cannot run VBA macros. Excel for Mac supports VBA, but UserForm and control compatibility can differ from Windows, so confirm the controls used by a workbook on the target Mac version.

Make sure the ListBox’s RowSource property is blank when using AddItem. A control bound to a range and one populated manually are different loading approaches; mixing them can cause errors or unexpected results. Save a copy of the workbook before running a macro, because VBA changes cannot be undone with Ctrl+Z.

Working Alphabetical-Sort Example

Paste this code into the UserForm module. When the form initializes, it finds the last nonempty cell in column A, reads the values into an array, sorts the array without regard to letter case, and adds the sorted values to the ListBox.

Option Explicit

Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim itemCount As Long
    Dim i As Long
    Dim names() As String

    Set ws = ThisWorkbook.Worksheets("Data")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    Me.lstNames.Clear
    If lastRow < 2 Then Exit Sub

    itemCount = lastRow - 1
    ReDim names(1 To itemCount)

    For i = 1 To itemCount
        names(i) = CStr(ws.Cells(i + 1, 1).Value2)
    Next i

    SortTextArray names

    For i = 1 To itemCount
        Me.lstNames.AddItem names(i)
    Next i
End Sub

Private Sub SortTextArray(ByRef items() As String)
    Dim i As Long
    Dim j As Long
    Dim currentItem As String

    ' Insert each value into its place in the sorted portion.
    For i = LBound(items) + 1 To UBound(items)
        currentItem = items(i)
        j = i - 1

        Do While j >= LBound(items)
            If StrComp(items(j), currentItem, vbTextCompare) <= 0 Then Exit Do
            items(j + 1) = items(j)
            j = j - 1
        Loop

        items(j + 1) = currentItem
    Next i
End Sub

For example, if A2:A5 contains “pear,” “Apple,” “orange,” and “Banana,” the ListBox displays Apple, Banana, orange, pear. The comparison uses StrComp with vbTextCompare, so upper- and lowercase letters do not create separate groups in the sort. The order of accented characters can depend on the system’s language settings.

The code assumes the cells contain ordinary text or values that can be converted to text. If the column may contain Excel error values, check for those before calling CStr; converting an error value to a string can raise a type mismatch. Empty cells become empty strings and may appear as blank ListBox rows, so remove or skip blanks if blank entries are not wanted.

Change the Sort Direction or Data Type

To sort text in descending order, change the comparison in the helper routine. The current condition shifts an earlier item when it compares greater than the item being inserted. Reversing that comparison produces descending order. Keep the comparison consistent throughout the sort rather than reversing the displayed list after loading it.

Numeric values need numeric comparison, not text comparison. Text values such as “1,” “10,” and “2” sort alphabetically as 1, 10, 2; that is not numerical order. If the source cells contain actual numbers, store them as numeric values in a Variant array and compare them numerically. If the cells contain text that represents numbers, validate and convert the values before sorting so that unexpected text does not cause a conversion error.

Date values have a similar consideration: a true Excel date is stored as a number and can be sorted chronologically as a numeric value. A date written as inconsistent text may sort in an order that looks wrong, because the routine is comparing characters rather than interpreting each string as a date. Standardize the source values or convert them to dates before comparing.

For locale-sensitive names, case-insensitive alphabetical order may not match every organization’s preferred rules for accents, punctuation, or surname prefixes. If a specific business convention matters, define the sort key explicitly—for example, a normalized comparison value—and keep the original display text separate.

Sort Rows Without Losing Their Relationships

A list with several columns needs more care than a single-column list. Suppose a ListBox shows a person’s name and department. Sorting only the names and then filling departments from the original worksheet order would pair some names with the wrong departments. The sort must move each complete record together, using the name as the key.

One approach is to load every field into a two-dimensional array, compare the chosen key field, and swap or shift the entire row of values whenever records move. Then add the first field with AddItem and set the remaining columns with the ListBox’s List property. Set the ListBox’s ColumnCount to match the number of displayed fields. Another approach is to sort the worksheet table as complete rows before reading it. In either case, sort entire records, not one column in isolation.

For a table with headers, choose the field that determines the order and decide how ties should be handled. A secondary sort key—for example, department and then employee name—makes repeated results predictable. If you need to retrieve a selected record later, consider storing a unique identifier in a hidden ListBox column. A displayed name alone may not uniquely identify a source row.

Using RowSource or Sorting the Worksheet

RowSource can be convenient when the ListBox should reflect a worksheet range directly. It does not perform the sort; sort the range first or point it at a separately prepared, ordered range. A separate output area can preserve the original row order, although it must be kept current as source records change. If the form must allow the user to add, remove, or rearrange items, manual loading with AddItem is often easier to control than a directly bound range.

Sorting the original worksheet is appropriate when changing the sheet’s row order is an intended part of the workflow. If other formulas, users, or processes depend on the current order, use a copy or an independent sorted result instead. A worksheet sort should include all columns belonging to each record; sorting only the names column can detach names from their associated data.

Excel 2021 and Microsoft 365 include the worksheet SORT function, which can return a sorted array without rearranging the source range. A UserForm still needs code to read the resulting cells and populate its control. Formula-based sorting is useful when the sorted view should update as source values change, but it will not automatically refresh an already-open ListBox unless the form reloads its items.

Common Problems and Practical Fixes

The list is still in source order: Confirm that the call to SortTextArray names happens before the loop that calls AddItem. Sorting after loading the control does not change its existing order by itself.
The form shows a blank row: Check for empty cells between the header and last used row. The sample reads every row in that span, including blanks. Skip values whose trimmed text is empty if they should not be listed.
The macro reports an invalid control or object: Verify the ListBox is named lstNames and the worksheet tab is named Data. VBA refers to the worksheet tab name in this example, not a guessed name.
The list repeats old items: Clear the ListBox before repopulating it, as the example does. If the form reloads data through another routine, clear it there too.
Numbers look out of order: Check whether the source contains numbers or text that looks like numbers. Text comparison gives a different order from numerical comparison.
The ListBox shows the wrong columns: For a multi-column list, set ColumnCount and populate each row’s fields together. Do not independently sort one displayed column.

If the data changes while the form is open, call a dedicated loading routine after the change rather than relying only on UserForm_Initialize. That event runs when the form is initialized, not every time a cell changes. Keep loading and sorting in a separate procedure if both form initialization and a refresh button need to use the same process.

When Another Method Is Better

For a short list that changes rarely, sorting a VBA array when the form opens is straightforward and keeps the worksheet intact. For a large table, a worksheet sort, a table-based workflow, or a prepared query result can be easier to maintain and more efficient. For a list whose order should update live as the user edits a cell, refresh the ListBox after the edit or display a formula-driven sorted range and reload the control when needed.

There is no single best method for every workbook. The important design choice is whether the ListBox is merely a view of existing data or whether its order is part of the workbook’s stored data. Keep the source and displayed values associated, especially when users select an item and the code must find the correct underlying record.

Frequently Asked Questions

Can I sort a VBA UserForm ListBox without changing the worksheet?

Yes. Read the source values into an array, sort that array, and add the sorted values to the ListBox. The example above changes the display order only; it does not sort or rewrite the worksheet cells.

Does a VBA ListBox have a Sorted property?

The standard MSForms ListBox used on an Excel UserForm does not provide a general-purpose Sorted property for automatic alphabetical ordering. Sort the values before adding them, or sort the range that supplies them.

How do I sort a ListBox numerically?

Compare numeric values as numbers, not as text. Text comparison can place 10 before 2. Store actual numeric values in the sorting data, or validate and convert numeric text before comparing it.

How do I keep multiple ListBox columns together while sorting?

Sort complete records using one field as the key. Move every field for a record together in the array, then populate all ListBox columns from that ordered data. Sorting just the visible key column can mismatch the related fields.

Will the ListBox resort when worksheet data changes?

Not automatically when it has already been populated. Run the loading and sorting routine again after the data changes, or close and reopen the form so its initialization code runs again.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.