Rifat Hassan
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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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.
➤ 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).
➤ Step 3: Click on OK. You’ll get a drop-down list with the country names created in cell B4 of the active worksheet.
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.
➤ 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
➤ 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.
➤ 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 the ascending order (A-Z). So I’ve put 1.
➤ 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 need.
Related Content: How to Sort Drop Down List in Excel (5 Easy Methods)
Similar Readings:
- [Fix] Excel Sort by Date Not Working (2 Causes with Solutions)
- Excel Sort and Ignore Blanks (4 Ways)
- How to Sort by Name in Excel (3 Examples)
- Merge Cells Using Excel Formula (8 Simple Ways)
- VBA to Sort Table in Excel (4 Methods)
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.
➤ 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.
➤ 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). And you’ll find the UserForm1 loaded in your worksheet, with a ListBox in the middle containing Spain, Germany, Italy, England, and France.
⧠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 have any changes.
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].
➤ 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
➤ 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.
➤ Step 5: The Userform will be loaded with the ListBox, with the items in the order Spain, Germani, Italy, England, and France.
➤ Step 5: Click the button A-Z. The ListBox will be sorted A-Z automatically.
➤ Step 6: Similarly, click Z-A. You’ll find the ListBox sorted Z-A.
Related Content: How to Sort Unique List in Excel (10 Useful Methods)
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.
