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

Get FREE Advanced Excel Exercises with Solutions!

In this article, I’ll show you how you can sort a listbox with VBA in Excel. You’ll learn to sort a listbox with VBA in an Excel worksheet, as well as in a VBA UserForm.


2 Suitable Ways to Sort ListBox with VBA in Excel

1. Sort ListBox in a Worksheet with VBA in Excel

First of all, I’ll show you how you can sort a listbox in an Excel worksheet with VBA.


1.1 How to Create ListBox in an Excel Worksheet

Before sorting the listbox, let’s have a quick glance at how you can create a listbox in a worksheet.

Let’s create a listbox with the country names Spain, Germany, Italy, England, and France in cell B4 of our worksheet.

➤ Step 1: Select cell B4 and go to the Data > Data Tools > Data Validation tool in the Excel toolbar.

Creating ListBox to Sort ListBox with VBA in Excel

➤ Step 2: Click on Data Validation. In the Data Validation dialogue box, under the Settings option, choose List from Allow. And in the Source box, put Spain,Germany,Italy,England,France (No space after the commas).

Data Validation to Sort Listbox with VBA in Excel

➤ Step 3:  Click on OK. You’ll get a drop-down list with the country names created in cell B4 of the active worksheet.

Read More: Create ListBox for Multiple Columns in Excel VBA


1.2 How to Sort the ListBox with VBA

We’ve created the listbox in the Excel worksheet. Now we’ll learn to sort the listbox with VBA.

➤ Step 1: Press ALT + F11 on your keyboard. The Visual Basic window will open. Go to Insert > Module to insert a new module in the window.

Inserting Module to Sort Listbox with VBA in Excel

➤ Step 2: A new module will open. Insert the following VBA code in the module.

⧭ VBA Code:

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

Entering Code to Sort Listbox with VBA in Excel

➤ Step 3: Come back to your worksheet and save the file as Excel Macro-Enabled Workbook.

➤ Step 4: Run the code by clicking the Run Sub/UserForm option in the VBA toolbar.

Running the Code to Sort Listbox with VBA in Excel

➤ Step 5: An Input Box will appear asking you to enter 1 or 2, depending on whether you want to sort in ascending or descending order. Here I want to sort in ascending order (A-Z). So I’ve put 1.

Entering Input to Sort Listbox with VBA in Excel

➤ Step 6: Finally click OK. You’ll get the listbox in your worksheet sorted in ascending order (England, France, Germany, Italy, Spain).

⧭ Note:

In the 6th and 37th lines of the code, we’ve used B4 because the list lies in cell B4 of our worksheet. You change it according to your needs.


2. Sort Listbox in an UserForm with VBA in Excel

Now I’ll show you how you can sort a listbox in an UserForm with VBA in Excel.

2.1 How to Create a Listbox in an Excel UserForm

First of all, let’s see how we can create a listbox in an UserForm with VBA.

➤ Step 1: Press ALT + F11 to open the Visual Basic window. Then go to Insert > UserForm in the toolbar to insert a new UserForm.

Inserting UserForm to Sort Listbox with VBA in Excel

➤ Step 2: A UserForm called UserForm1 will be created in the editor. On the left of the UserForm, you’ll get a box of tools called Toolbox. Move your cursor over the toolbox for the Listbox. And when you get it, drag it over to the UserForm.

Creating ListBox to Sort Listbox with VBA in Excel

➤ Step 3: Create a new Module following Step 1 of section 1.2, and put the following code there.

⧭ VBA 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

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

UserForm with ListBox to Sort Listbox with VBA in Excel

⧭ Notes:

In the code, UserForm1 is the name of the UserForm. Similarly, ListBox1 is the name of the Listbox, and Spain, Germany, Italy, England, and France are the names that we wanted to put in the Listbox. Change these in the code if you want to make any changes.

Read More: How to Create Multi Select ListBox in Excel


2.2 How to Sort the Listbox with VBA

We’ve seen how to create a Listbox in a UserForm with VBA. Now we’ll see how we can sort the Listbox.

➤ Step 1: Open the UserForm in VBA and drag two Command buttons from the toolbox over it. I’ve changed their displays to A-Z and Z-A.

[We’ve changed only the displays of the buttons, not the names. They are named CommandButton1 and CommandButton2].

Adding Buttons to Sort Listbox with VBA in Excel

➤ Step 2: Click on the Command button A-Z. You’ll find a Private Subprocedure titled as CommandButton1_Click. Enter the following code there.

⧭ VBA Code:

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

➤ Step 3: Similarly, click on the Command button Z-A. You’ll find another Private Subprocedure titled as CommandButton2_Click. Enter the following code there.

⧭ VBA Code:

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

VBA Code to Sort Listbox with VBA in Excel

➤ Step 4: Your UserForm is now ready to use with the Listbox. Go to the module Load_UserForm (Step 3 of Section 2.1) and run it.

Running Module to Sort Listbox with VBA in Excel

➤ Step 5: The Userform will be loaded with the ListBox, with the items in the order Spain, Germany, Italy, England, and France.

➤ Step 5: Click the button A-Z. The ListBox will be sorted A-Z automatically.

Sort ListBox A-Z with VBA in Excel

➤ Step 6: Similarly, click Z-A. You’ll find the ListBox sorted Z-A.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

So, these are the ways to sort a listbox with VBA in Excel, both in the worksheet and in the UserForm. Do you have any questions? Feel free to ask us.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Rifat Hassan
Rifat Hassan

Rifat Hassan, BSc, Electrical and Electronic Engineering, Bangladesh University of Engineering and Technology, has worked with the ExcelDemy project for almost 2 years. Within these 2 years, he has written over 250 articles. He has also conducted a few Boot Camp sessions on effective coding, especially Visual Basic for Applications (VBA). Currently, he is working as a Software Developer to develop and deploy additional add-ins to enhance the customers with a more sophisticated experience with Microsoft Office Suits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo