Sorting ListBoxes with VBA

I received a question from a reader recently, who wanted to sort a Listbox using VBA.  As this seemed like a common problem which many users could face, I decided to dedicate a bit of time to create a more reusable solution.

In this post, we will look at two different types of Listboxes:

• UserForm Listboxes for custom VBA solutions
• Form Control Listboxes for worksheet based solutions

This post may seem like a lot of VBA code, but are comments inserted into the code to understand what is happening.  Within the text I included my thought process, to illustrate how I approached the scenario.  Whether my approach is right or wrong, it’s how I think about VBA.

There are four sort methods covered in this post:

• Standard order (the order the elements are added to the list box)
• Reverse order (the reverse of the standard order)
• A-Z sort
• Z-A sort

Re-usable code

One of the key principles of this little task was for the code to be as re-usable as possible.  Therefore in approaching this I sought to apply the following principles:

• The code which performs the main actions is contained within a standard module
• The code contained within the UserForm module intercepts the events (i.e. button clicks) then calls a macro from the standard module
• The variables used for the macros within the standard module are passed as arguments when calling the macro
• The list is re-created each time it is sorted, no matter which sort method is selected.  This ensures the list is updated for any changes whenever it is sorted.  The piece of reusable code which clears and re-creates the list I am calling the “resetMacro”.

Listboxes on UserForms

The screenshot below shows the UserForm, it contains:

• a ListBox
• four buttons for the different sort options
• a close button

VBA code included in a standard module

The following is the code to include in the standard module, which performs the main actions.

Display the UserForm

The first code, will simply display the UserForm when a button is clicked.

Sub ShowUserForm()

'Show the user form
frmListBoxExample.Show

End Sub

Populate the ListBox

The following code will list all the worksheets in a ListBox.  A variable referencing the specific ListBox is passed to the macro at run-time, therefore the code can be used with any ListBox.  In our example, this VBA code is used:

• when the UserForm is initialized
• when the btnStandard button is clicked
• within all the other sort macros to recreate the list.

Sub UserFormList(myListBox As MSForms.ListBox)

'Create an object variable to hold worksheet as an object
Dim ws As Worksheet

'Clear the listBox
myListBox.Clear

'Loop through each worksheet add to listBox
For Each ws In ThisWorkbook.Worksheets

    myListBox.AddItem ws.Name

Next ws

End Sub

Reverse the items in the ListBox

The following code will first call the “resetMacro” to refresh the ListBox, then sort it in reverse order.

Sub UserFormReverseList(myListBox As MSForms.ListBox, Optional resetMacro As String)

'Create variables
Dim ListArray() As String
Dim i As Integer

'Reset the listBox into standard order
If resetMacro <> "" Then
    Run resetMacro, myListBox
End If

'Resize the Array variable
ReDim ListArray(0 To myListBox.ListCount - 1)

'Loop through the listBox and reverse the order into an Array
For i = 0 To myListBox.ListCount - 1

    ListArray(i) = myListBox.List(myListBox.ListCount - 1 - i)

Next i

'Clear the listBox
myListBox.Clear

'Fill the list box with the Array
myListBox.List = ListArray

End Sub

A-Z sort the List Box

The following code will first call the “resetMacro” to refresh the ListBox, then sort it in A-Z order using the Bubble sort method.

Sub UserFormSortAZ(myListBox As MSForms.ListBox, Optional resetMacro As String)

'Create variables
Dim j As Long
Dim i As Long
Dim temp As Variant

'Reset the listBox into standard order
If resetMacro <> "" Then
    Run resetMacro, myListBox
End If

'Use Bubble sort method to put listBox in A-Z order
With myListBox
    For j = 0 To .ListCount - 2
        For i = 0 To .ListCount - 2
            If LCase(.List(i)) > LCase(.List(i + 1)) Then
                temp = .List(i)
                .List(i) = .List(i + 1)
                .List(i + 1) = temp
            End If
        Next i
    Next j
End With

End Sub

If you notice there is a -2 in the code, which appears odd.  So why is it there and what does it do?

• Using the Bubble Sort method, the last item does not need to be sorted.  If every other item in the list is sorted correctly, then the last item must also be in the correct place (so that is the first -1).
• The first item in a UserForm ListBox is item 0, rather than 1.  Therefore, as the list starts at zero, we can -1 from the count to ensure we loop through all the items once.

Note: When using a Form Control list box, the first list position is 1, so the Form Control VBA code further down the post only has -1, rather than -2.

Z-A sort the ListBox

The following code will first call the “resetMacro” to refresh the ListBox, then sort it in Z-A order using the Bubble sort method.

Sub UserFormSortZA(myListBox As MSForms.ListBox, Optional resetMacro As String)

'Create variables
Dim j As Long
Dim i As Long
Dim temp As Variant

'Reset the listBox into standard order
If resetMacro <> "" Then
    Run resetMacro, myListBox
End If

'Use Bubble sort method to put listBox in Z-A order
With myListBox
    For j = 0 To .ListCount - 2
        For i = 0 To .ListCount - 2
            If LCase(.List(i)) < LCase(.List(i + 1)) Then
                temp = .List(i)
                .List(i) = .List(i + 1)
                .List(i + 1) = temp
            End If
        Next i
    Next j
End With

End Sub

VBA code included in the UserForm code module

The following sections of code are all included within the UserForm module.  The code here is primarily to call the macros in the standard module, which we covered in the section above.

The name of the ListBox in the example is lstListBox.

Initialize the UserForm

When the UserForm first opens the UserForm_Initialize code executes.  In our example, when the UserForm opens it calls the UserFormList macro, passing the lstListBox as an object variable into the macro.

Private Sub UserForm_Initialize()

Call UserFormList(Me.lstListBox)

End Sub

Clicking the btnClose Button

The close button with Unload the UserForm.

Private Sub btnClose_Click()

Unload Me

End Sub

Clicking the btnStandard button

The first button calls the macro to return the list to its original order.  The code UserFormList is called, passing the lstListBox as an object variable.

Private Sub btnStandard_Click()

Call UserFormList(Me.lstListBox)

End Sub

Clicking the btnReverse button

The second button calls the macro to reverse the order of the list.  In our example, the code UserFormReverseList is called.  Two arguments are passed (1) the ListBox object (2) the name of the resetMacro.

Private Sub btnReverse_Click()

Call UserFormReverseList(Me.lstListBox, "UserFormList")

End Sub

Clicking the btnAZ button

The third button calls the macro to sort the list in A-Z order.  The code UserFormSortAZ is called.  Two arguments are passed (1) the ListBox object (2) the name of the “resetMacro”.

Private Sub btnAZ_Click()

Call UserFormSortAZ(Me.lstListBox, "UserFormList")

End Sub

Clicking btnZA button

The fourth button calls the macro to sort the list in Z-A order.  The code UserFormSortZA is called.  Two arguments are passed (1) the ListBox object (2) the “resetMacro”.

Private Sub btnZA_Click()

Call UserFormSortZA(Me.lstListBox, "UserFormList")

End Sub

That now completes the code used to sort a ListBox contained within a UserForm.

Form Control Listboxes on the worksheet

The code in this section is for use with a Form Control ListBox.

Each button is assigned to a macro requiring arguments, as a result they do not appear within the Assign Macro box.  Click here to learn more about this technique.

The macros assigned to each button is as follows:

• Standard sort: FormControlList “lstListBox”‘
• Reverse sort: FormControlReverse “lstListBox”, “FormControlList”‘
• A-Z sort: FormControlSortAZ “lstListBox”, “FormControlList”‘
• Z-A sort: FormControlSortZA “lstListBox”, “FormControlList”‘

VBA code included in a standard module

The following code is all included in the standard module.

Populate the ListBox

The code below will list all the worksheets in a ListBox.  The specific ListBox is passed to the macro as an argument, therefore this can be used with any ListBox.  This macro is called:

• when the btnStandard button is clicked
• within all the other sort macros to recreate the list (i.e. the “resetMacro”).

Sub FormControlList(myListBoxName As String)

'Create Object variable to hold the listBox
Dim myListBox As Object

'Set the variable to the worksheet and listBox called
Set myListBox = ActiveSheet.ListBoxes(myListBoxName)

'Create an object variable to hold worksheet as an object
Dim ws As Worksheet

'Clear the listBox
myListBox.RemoveAllItems

'Loop through each worksheet add to listBox
For Each ws In ThisWorkbook.Worksheets

    myListBox.AddItem ws.Name

Next ws

End Sub

Reverse the items in the ListBox

The following code will first call the “resetMacro” to update the ListBox, then sort it in reverse order.

Sub FormControlReverse(myListBoxName As String, Optional resetMacro As String)

'Create variables
Dim ListArray() As String
Dim i As Integer
Dim myListBox As ListBox

'Set the variable to the worksheet and listBox called
Set myListBox = ActiveSheet.ListBoxes(myListBoxName)

'Reset the listBox into standard order
If resetMacro <> "" Then
    Run resetMacro, myListBoxName
End If

'Resize the Array variable
ReDim ListArray(1 To myListBox.ListCount)

'Loop through the listBox and reverse the order into an Array
For i = 1 To myListBox.ListCount

    ListArray(i) = myListBox.List(myListBox.ListCount - i + 1)

Next i

'Clear the listBox
myListBox.RemoveAllItems

'Fill the list box with the Array
myListBox.List = ListArray

End Sub

A-Z sort the List Box

The following code will first call the “resetMacro” to update the ListBox, then sort it in A-Z order using the Bubble sort method.

Sub FormControlSortAZ(myListBoxName As String, Optional resetMacro As String)

'Create variables
Dim j As Long
Dim i As Long
Dim temp As Variant
Dim myListBox As ListBox

'Set the variable to the worksheet and listBox
Set myListBox = ActiveSheet.ListBoxes(myListBoxName)

'Reset the listBox into standard order
If resetMacro <> "" Then
    Run resetMacro, myListBoxName
End If

'Use Bubble sort method to put listBox in A-Z order
With myListBox
    For j = 1 To .ListCount - 1
        For i = 1 To .ListCount - 1
            If LCase(.List(i)) > LCase(.List(i + 1)) Then
                temp = .List(i)
                .List(i) = .List(i + 1)
                .List(i + 1) = temp
            End If
        Next i
    Next j
End With

End Sub

Z-A sort the List Box

The following code will first call the “resetMacro” to update the ListBox, then sort it in Z-A order using the Bubble sort method.

Sub FormControlSortZA(myListBoxName As String, Optional resetMacro As String)

'Create variables
Dim j As Long
Dim i As Long
Dim temp As Variant
Dim myListBox As ListBox

'Set the variable to the worksheet and listBox
Set myListBox = ActiveSheet.ListBoxes(myListBoxName)

'Reset the listBox into standard order
If resetMacro <> "" Then
    Run resetMacro, myListBoxName
End If

'Use Bubble sort method to put listBox in A-Z order
With myListBox
    For j = 1 To .ListCount - 1
        For i = 1 To .ListCount - 1
            If LCase(.List(i)) < LCase(.List(i + 1)) Then
                temp = .List(i)
                .List(i) = .List(i + 1)
                .List(i + 1) = temp
            End If
       Next i
    Next j
End With

End Sub

Conclusion

In this post, we have seen two methods to sort ListBoxes using VBA using reusable code segments.  Obviously, to be useful this needs to be part of a bigger project.  Feel free to take this and adapt it as you need to.

---

What next?

Discover how you can automate your work with our Excel courses and tools.

The Excel Academy