Hosne Ara
The ComboBox is a special feature of Excel. If we ever need to choose from multiple options, VBA ComboBox is there for the job. Not only that, but we can also sort the ComboBox list alphabetically or serially. In this article, I am going to write in detail about how to sort ComboBox list alphabetically in Excel VBA.
The above image is an overview of sorting the Excel VBA ComboBox list alphabetically. This VBA code applies a comparison between the options to check which option has a lesser value to sort them. This is how the ComboBox options are sorted alphabetically.
Download Practice Workbook
You can download the practice workbook here.
How to Launch VBA UserForm in Excel
There are various methods by which you can open the VBA editor. The most commonly used method is Developer Tab >> Visual Basic and the simplest way to launch the VBA editor is to press ALT+F11.
If you don’t have the Developer tab in your Excel workbook, follow the below steps:
- First, right-click anywhere on the Tab section. Then, click on Customize the Ribbon…
- Go to Customize Ribbon >> Developer. Next, select it and finally click OK.
Overview of VBA ComboBox
ComboBox is a drop-down list based on the combination of a certain text box. From the drop-down list, we can choose our desired option. Excel ComboBox is available from Excel 2007 to 365.
Steps to Sort Excel VBA ComboBox List Alphabetically
I have a dataset of a bookstore named Penguin Random House. The attributes of this dataset are Book Title, Author, and Price. At first, I will create a ComboBox based on the column Book Title, then sort the ComboBox list alphabetically. Here, my Excel version is Microsoft Excel 365.
Our goal is to add the book titles from the Book Title column to the ComboBox and then sort them in alphabetical order.
Step 1: Creating UserForm ComboBox Outline
In the beginning, I will create an UserForm ComboBox outline.
- Press the Alt+F11 shortcut to open the Visual Basic window.
- Now, a blank Visual Basic window will appear. Go to Insert >> UserForm to create a UserForm.
- Alongside the UserForm, Toolbox, and Properties will appear. We will use them according to our need.
- First, I will change the UserForm name to UserForm ComboBox in the Caption section. You can name the title as your wish.
- I will also add a Label and ComboBox to the UserForm.
- You can simply click on Label1 and use the Caption area to change the Label name to Single Column ComboBox.
Step 2: Populating ComboBox with Book Titles
In this step, I will write VBA code to populate ComboBox with the values of the Book Title column.
- Double-click on the UserForm or right-click anywhere on the UserForm to go to View Code.
- After that, the coding space will open up. Now delete the code you find here and type the following VBA code to add ComboBox options for the book title column.
Private Sub UserForm_Initialize()
Dim values As Variant
Dim lastRow As Long
lastRow = ThisWorkbook.Sheets("SortBookTitle").Cells(Rows.Count, "B").End(xlUp).Row
values = ThisWorkbook.Sheets("SortBookTitle").Range("B5:B" & lastRow).Value
Me.ComboBox1.List = values
End Sub- The below image shows the UserForm_Initialize Sub procedure in Visual Basic.
Code Breakdown
Private Sub UserForm_Initialize()→ This line defines a Private Sub procedure named “UserForm_Initialize”, which is an event handler that runs automatically when the user form is initialized
Values, and lastRow→ Variable declaration. Values is a variant type variable and lastRow is a Long type variable.
lastRow = ThisWorkbook.Sheets("SortBookTitle").Cells(Rows.Count, "B").End(xlUp).Row→ This line is finding out the last row number of column B and stores it in lastRow. SortBookTitle is the worksheet name.
values = ThisWorkbook.Sheets("SortBookTitle").Range("B5:B" & lastRow).Value→ Values contain values from the start to the end of column B. This is an array of values.
Me.ComboBox1.List = values→ This line is assigning array values from Values to the list of the ComboBox1.
End Sub→ This line is to end the Sub Procedure.
Step 3: Sorting ComboBox List Alphabetically
So, adding items or values to the ComboBox is easy but our goal is to sort the items alphabetically.
- Change the Label caption to Sorted ComboBox and Type the following modified code for sorting the ComboBox list alphabetically:
Private Sub UserForm_Initialize()
Dim values As Variant
Dim lastRow As Long
Dim i As Long, j As Long
Dim temp As Variant
lastRow = ThisWorkbook.Sheets("SortBookTitle").Cells(Rows.Count, "B").End(xlUp).Row
values = ThisWorkbook.Sheets("SortBookTitle").Range("B5:B" & lastRow).Value
For i = LBound(values, 1) To UBound(values, 1)
For j = i + 1 To UBound(values, 1)
If values(j, 1) < values(i, 1) Then
temp = values(i, 1)
values(i, 1) = values(j, 1)
values(j, 1) = temp
End If
Next j
Next i
Me.ComboBox1.List = values
End Sub- The below image shows modified the UserForm_Initialize Sub procedure in Visual Basic.
Code Breakdown
This code has a part where a nested loop is used to perform a simple sorting algorithm on a one-dimensional array of values.
For i = LBound(values, 1) To UBound(values, 1)→ This loop will continue to run from the lower bounds of the array to the upper bounds of the array. With each iteration, this loop will read each value from the array.
For j = i + 1 To UBound(values, 1)→ This nested loop will compare each element of the array “values” with every other element in the array, starting with the element at position “i+1”.
If values(j, 1) < values(i, 1) Then→ This line checks whether the value at position “j” in the array is less than the value at position “i”.
The following lines are used to swap the values at positions “i” and “j” in the array if the condition satisfies:
temp = values(i, 1)
values(i, 1) = values(j, 1)
values(j, 1) = tempThe value at position “i” is stored in a temporary variable called “temp”, then the value at position “j” is copied to position “i”, and finally the value in “temp” is copied to position “j”.
Next j→ This line moves to the next element in the inner loop.
Next i→ This line moves to the next element in the outer loop.
- Finally, Run the code.
- Now, the ComboBox list will look like the following image which is sorted alphabetically.
How to Sort Data Alphabetically in Multi-Column ComboBox with Excel VBA
In this section, I will show how to sort data alphabetically in a multi-column ComboBox. In VBA, it’s not possible to directly display multiple columns in a ComboBox. Thus, I concatenated the values from columns B, C, and D into a single string, and then added that string to the ComboBox.
You can create another UserForm as earlier mentioned. Then, type the following modified VBA code:
Private Sub UserForm_Initialize()
Dim values As Variant
Dim lastRow As Long
Dim i As Long, j As Long
Dim temp As Variant
lastRow = ThisWorkbook.Sheets("SortMultipleColumns").Cells(Rows.Count, "B").End(xlUp).Row
values = ThisWorkbook.Sheets("SortMultipleColumns").Range("B5:D" & lastRow).Value
For i = LBound(values, 1) To UBound(values, 1)
For j = i + 1 To UBound(values, 1)
If values(j, 1) < values(i, 1) Then
temp = values(i, 1)
values(i, 1) = values(j, 1)
values(j, 1) = temp
temp = values(i, 2)
values(i, 2) = values(j, 2)
values(j, 2) = temp
temp = values(i, 3)
values(i, 3) = values(j, 3)
values(j, 3) = temp
End If
Next j
Next i
For i = LBound(values, 1) To UBound(values, 1)
Me.ComboBox1.AddItem values(i, 1) & " - " & values(i, 2) & " - " & values(i, 3)
Next i
End Sub- The below image shows the UserForm_Initialize Sub procedure in Visual Basic.
Code Breakdown
The modification in the code was to swap other column values in the same row along with the Book Title column. So, the array “values” now has three dimensions instead of one as we have three columns in this dataset.
The following code made the swapping:
temp = values(i, 1)
values(i, 1) = values(j, 1)
values(j, 1) = temp
temp = values(i, 2)
values(i, 2) = values(j, 2)
values(j, 2) = temp
temp = values(i, 3)
values(i, 3) = values(j, 3)
values(j, 3) = tempThe first three lines swap the values in the first dimension of the array. The next three lines swap the values in the second dimension of the array. The last three lines swap the values in the third dimension of the array.
For i = LBound(values, 1) To UBound(values, 1)
Me.ComboBox1.AddItem values(i, 1) & " - " & values(i, 2) & " - " & values(i, 3)
Next iThese lines concatenate the values from columns B, C, and D into a single string and add them to the ComboBox.
- Next, Run the code.
- Now, the multi-column ComboBox will look like the following image which is sorted alphabetically.
How to Sort by Column Header Name in Excel VBA
In this part of the article, I am going to short values based on the header selection. After that, I will extract any selection from the ComboBox to the worksheet. I created an outline and a UserForm button in the worksheet to initiate the VBA code like the image below:
- I will create another outline where two ComboBox is used. Alongside them, I will add a command button to extract the selection from the comboBox options.
- Here, I will need to populate ComboBox1 with header names and ComboBox2 with the column values according to the header selection from ComboBox1. After that, sort them alphabetically based on the header column.
- Finally, I will add VBA codes to the CommandButton1 to write selected information from ComboBox2 to the desired worksheet cells.
- Type the following VBA code:
Private Sub UserForm_Initialize()
Dim header As Range
For Each header In Worksheets("Bookstore").Range("B4:D4").Cells
ComboBox1.AddItem header.Value
Next header
End SubPrivate Sub ComboBox1_Change()
Dim lastRow As Long
Dim headerRow As Long
Dim headers As Variant
Dim header As Variant
Dim columnNum As Long
Dim data As Variant
Dim i As Long
Dim rowData As String
Dim arrData() As String
header = Me.ComboBox1.Value
headerRow = 4
headers = ThisWorkbook.Sheets("Bookstore").Range("B4:D4").Value
For i = 1 To UBound(headers, 2)
If headers(1, i) = header Then
columnNum = i
Exit For
End If
Next i
Me.ComboBox2.Clear
lastRow = 15
data = ThisWorkbook.Sheets("Bookstore").Range("B5:D" & lastRow).Value
For i = 1 To UBound(data)
rowData = data(i, columnNum)
For j = 1 To UBound(data, 2)
If j <> columnNum Then
rowData = rowData & " - " & data(i, j)
End If
Next j
Me.ComboBox2.AddItem rowData
Next i
ReDim arrData(0 To Me.ComboBox2.ListCount - 1)
For i = 0 To Me.ComboBox2.ListCount - 1
arrData(i) = Me.ComboBox2.List(i)
Next i
Call QuickSort(arrData, LBound(arrData), UBound(arrData))
Me.ComboBox2.Clear
For i = 0 To UBound(arrData)
Me.ComboBox2.AddItem arrData(i)
Next i
End SubSub QuickSort(arrData() As String, intLow As Long, intHigh As Long)
Dim intPivot As Long
Dim strTmp As String
Dim intL As Long
Dim intH As Long
intL = intLow
intH = intHigh
intPivot = (intLow + intHigh) \ 2
Do While intL <= intH
Do While arrData(intL) < arrData(intPivot) And intL < intHigh
intL = intL + 1
Loop
Do While arrData(intH) > arrData(intPivot) And intH > intLow
intH = intH - 1
Loop
If intL <= intH Then
strTmp = arrData(intL)
arrData(intL) = arrData(intH)
arrData(intH) = strTmp
intL = intL + 1
intH = intH - 1
End If
Loop
If intLow < intH Then Call QuickSort(arrData, intLow, intH)
If intL < intHigh Then Call QuickSort(arrData, intL, intHigh)
End SubPrivate Sub CommandButton1_Click()
If Me.ComboBox2.Value = "" Then
MsgBox "Please select a value from the dropdown list.", vbExclamation, "No Selection"
Exit Sub
End If
Dim selectedValue As String
Dim selectedRowData As Variant
selectedValue = Me.ComboBox2.Value
selectedRowData = Split(selectedValue, " - ")
ThisWorkbook.Sheets("Bookstore").Range("B18").Value = selectedRowData(0)
ThisWorkbook.Sheets("Bookstore").Range("C18").Value = selectedRowData(1)
ThisWorkbook.Sheets("Bookstore").Range("D18").Value = selectedRowData(2)
Unload Me
End SubCode Breakdown
UserForm_Initialize→ is a Private Sub procedure, that populates ComboBox1 with header names.
ComboBox1_Change→ is a Private Sub procedure, which is triggered whenever there is any change in ComboBox1. This subroutine takes a selected header from ComboBox1, retrieves the corresponding data from the worksheet, concatenates the values from each row into a single string, adds the strings to ComboBox2, sorts the strings, and then adds the sorted strings back to ComboBox2.
QuickSort→ is a Subroutine, which sorts the elements of the input array arrData() in ascending order using the QuickSort algorithm. This subroutine is called by the Sub Procedure ComboBox1_Change.
CommandButton1_Click→ is a Subroutine that is executed when the CommandButton1 (named Extract Data) is clicked. This Subroutine retrieves the selected value from ComboBox2, split it into three parts, and writes each part into the desired cells. Finally, the UserForm is unloaded to close the form.
- Next, Run the code.
Frequently Asked Questions (FAQs)
1. How do you custom-sort in Excel VBA?
There is a built-in Custom Sort… feature available in Excel. Apart from that, if you want to custom-sort with Excel VBA, you can use the Range.Sort method to custom-sort a range of cells based on one or more criteria. Here is an example for better understanding:
Sub CustomSort()
Dim MyRange As Range
Set MyRange = Range("A1:C10")
MyRange.Sort Key1:=Range("A1"), Order1:=xlDescending, _
Key2:=Range("B1"), Order2:=xlAscending, _
Header:=xlYes
End SubHere, MyRange is the range of cells that we want to sort. The Key1 argument is the first sorting criterion, which holds Column A. The Order1 argument takes the sorting order for the first criterion, which is descending (xlDescending) in my example. The second sorting criterion Key2 argument is set to Column B and the Order2 argument is set to sort the Key2 argument in ascending (xlAscending) order. Finally, the Header argument is set to xlYes, which indicates that the range has a header row.
You can customize the sorting criteria and range as per your requirement.
2. How do I sort descending in VBA?
To sort a range of cells in descending order in VBA, set the Order argument to xlDescending in the Range.Sort method. A simple example is provided below:
Sub SortDescending()
Dim MyRange As Range
Set MyRange = Range("A1:C10")
MyRange.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlYes
End Sub3. How do I sort the ComboBox list in descending order in Excel VBA?
Sorting in descending order is easy. You only need to change the logic where the comparison happens. Our logic code for sorting simple column ComboBox is:
For i = LBound(values, 1) To UBound(values, 1)
For j = i + 1 To UBound(values, 1)
If values(j, 1) < values(i, 1) Then
temp = values(i, 1)
values(i, 1) = values(j, 1)
values(j, 1) = temp
End If
Next j
Next iIf we change the logic operator (<) to the greater logic operator (>), The condition will now check which value is greater and sort them accordingly. So, the logic of the code will be like this:
For i = LBound(values, 1) To UBound(values, 1)
For j = i + 1 To UBound(values, 1)
If values(j, 1) > values(i, 1) Then
temp = values(i, 1)
values(i, 1) = values(j, 1)
values(j, 1) = temp
End If
Next j
Next iEverything else on the VBA code will remain the same.
4. How do you edit a ComboBox in Excel VBA?
To edit a ComboBox in Excel VBA, you can use the properties and methods of the ComboBox object.
Some of the common properties and methods of the ComboBox object:
List: This property gets or sets an array that contains the list of items in the ComboBox.
Value: This property gets or sets the value of the selected item in the ComboBox.
AddItem: This method adds an item to the list of items in the ComboBox.
Clear: This method removes all items from the list of items in the ComboBox.
RemoveItem: This method removes an item from the list of items in the ComboBox.
ListIndex: This property gets or sets the index of the selected item in the ComboBox.
Clear: This method removes all items from the ComboBox.
You can use other properties and methods of the ComboBox object to edit the ComboBox as per your requirement.
Key Takeaways from the Article
- Introduction to VBA ComboBox.
- How to sort single-column ComboBox alphabetically.
- How to sort multi-column ComboBox alphabetically.
- How to sort multi-column ComboBox alphabetically based on the column header name.
Things to Remember
- If there are numbers and strings in the column values, string values will appear first by default.
- In my solution to the problem, sort by a column header name, I had to extract values from the UserForm to the same worksheet, thus I moved to a static cell reference instead of a dynamic cell reference. With dynamic cell reference, VBA code reads information all the way down where there is no information.
Conclusion
In this article, you learned how to Sort Excel VBA ComboBox List Alphabetically. In addition to the single-column sorting, you learned how to sort multi-column ComboBox and sorting ComboBox based on the header name. Follow our ExcelDemy page for regular tips and tricks regarding Excel. You can suggest your thoughts in the comments below.
