How to Sort a ListBox with VBA in Excel (A Complete Guide)

Method 1 – Sort a ListBox in a Worksheet

1.1 Creating a ListBox in an Excel Worksheet

Before sorting the ListBox, let’s quickly go over how to create one in a worksheet:

Select cell B4.
Go to the Data tab, then click Data Tools and choose Data Validation.

Creating ListBox to Sort ListBox with VBA in Excel

In the Data Validation dialog box, under Settings, select List from the Allow dropdown.
In the Source box, enter the country names (without spaces after the commas): Spain,Germany,Italy,England,France.

Data Validation to Sort Listbox with VBA in Excel

Click OK to create a drop-down list with the country names in cell B4.

1.2 Sorting the ListBox with VBA

Now that we’ve created the ListBox, let’s learn how to sort it using VBA:

Press ALT + F11 to open the Visual Basic window.
Insert a new module by going to Insert > Module.

Enter the following VBA code to the module:

Sub Sort_ListBox()
Ascending = "Enter 1 to Sort in Ascending Order (A-Z)."
Descending = "Enter 2 to Sort in Descending Order (Z-A)."
Ascending_or_Descending = Int(InputBox(Ascending + vbNewLine + vbNewLine + "OR" + vbNewLine + vbNewLine + Descending))
Data = Range("B4").Validation.Formula1
Data = Split(Data, ",")
Range("B4").Validation.Delete
If Ascending_or_Descending = 1 Then
    For i = LBound(Data) To UBound(Data)
        For j = i + 1 To UBound(Data)
            If UCase(Data(i)) > UCase(Data(j)) Then
                Store = Data(j)
                Data(j) = Data(i)
                Data(i) = Store
            End If
        Next j
    Next i
ElseIf Ascending_or_Descending = 2 Then
    For i = LBound(Data) To UBound(Data)
        For j = i + 1 To UBound(Data)
            If UCase(Data(i)) < UCase(Data(j)) Then
                Store = Data(j)
                Data(j) = Data(i)
                Data(i) = Store
            End If
        Next j
    Next I
Else
    MsgBox "Enter a Valid Arguemnt (Either 1 or 2).", vbExclamation
End If
New_Data = ""
For i = LBound(Data) To UBound(Data) - 1
    New_Data = New_Data + Data(i) + ","
Next i
New_Data = New_Data + Data(UBound(Data))
Range("B4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:=New_Data
End Sub

Save the file as an Excel Macro-Enabled Workbook.

Run the code by clicking Run Sub/UserForm in the VBA toolbar.

An Input Box will appear, asking you to enter 1 or 2 (for ascending or descending order). Enter 1 for ascending (A-Z).

Click OK. Your ListBox in the worksheet will now be sorted in ascending order (England, France, Germany, Italy, Spain).

Note:

Adjust the cell reference (currently B4) according to your needs in lines 6 and 37 of the code.

Method 2 – Sort a Listbox in a UserForm

2.1 Creating a Listbox in an Excel UserForm

Let’s start by creating a ListBox within an Excel UserForm using VBA:

Press ALT + F11 to open the Visual Basic window.
Go to Insert > UserForm in the toolbar to insert a new UserForm.

A UserForm named UserForm1 will be created. In the toolbox on the left, find the ListBox tool and drag it onto the UserForm.

Create a new module (following Step 1 of section 1.2) and enter the following code:

Sub Load_UserForm()

UserForm1.ListBox1.AddItem "Spain"
UserForm1.ListBox1.AddItem "Germany"
UserForm1.ListBox1.AddItem "Italy"
UserForm1.ListBox1.AddItem "England"
UserForm1.ListBox1.AddItem "France"

Load UserForm1
UserForm1.Show

End Sub

Run the module called Load_UserForm (following Step 4 of section 1.2). You’ll see UserForm1 loaded in your worksheet, with a ListBox containing Spain, Germany, Italy, England, and France.

Notes:

In the code, UserForm1 refers to the UserForm, ListBox1 is the name of the ListBox, and the country names are the items we want in the ListBox. Modify these names as needed.

2.2 Sorting the Listbox with VBA

Now let’s learn how to sort the ListBox:

Open the UserForm in VBA and drag two Command buttons from the toolbox onto it. Change their displays to “A-Z” and “Z-A” (they are named CommandButton1 and CommandButton2).

Click on the A-Z button. You’ll find a Private Sub procedure titled CommandButton1_Click. Enter the following code there:

Private Sub CommandButton1_Click()

Lists = ""

For i = 0 To ListBox1.ListCount - 2
    Lists = Lists + ListBox1.Column(0, i) + ","
Next i

Lists = Lists + ListBox1.Column(0, ListBox1.ListCount - 1)
Lists = Split(Lists, ",")

For i = LBound(Lists) To UBound(Lists)
    For j = i + 1 To UBound(Lists)
        If UCase(Lists(i)) > UCase(Lists(j)) Then
            Store = Lists(j)
            Lists(j) = Lists(i)
            Lists(i) = Store
        End If
    Next j
Next i

For i = 0 To ListBox1.ListCount - 1
    ListBox1.Column(0, i) = Lists(i)
Next i

End Sub

Your UserForm is now ready to use with the ListBox. Run the Load_UserForm module.

Private Sub CommandButton2_Click()

Lists = ""

For i = 0 To ListBox1.ListCount - 2
    Lists = Lists + ListBox1.Column(0, i) + ","
Next i

Lists = Lists + ListBox1.Column(0, ListBox1.ListCount - 1)
Lists = Split(Lists, ",")

For i = LBound(Lists) To UBound(Lists)
    For j = i + 1 To UBound(Lists)
        If UCase(Lists(i)) < UCase(Lists(j)) Then
            Store = Lists(j)
            Lists(j) = Lists(i)
            Lists(i) = Store
        End If
    Next j
Next i

For i = 0 To ListBox1.ListCount - 1
    ListBox1.Column(0, i) = Lists(i)
Next i

End Sub