Sort ComboBox Data Using VBA

We can sort data in an Excel worksheet and a combobox using VBA. The process involves populating the combobox with unique data from the Excel worksheet as soon as it is entered and then sorting the data on workbook open procedure. Now the data gets sorted in the Excel worksheet and in the combobox as well. The VBA code can also be used with a ListBox.

The complete VBA code is given below:

Private Sub Workbook_Open()
ActiveWorkbook.Worksheets(“Sheet1”).Sort.SortFields.Add Key:=Range(“A:A”), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets(“sheet1”).Sort
.SetRange Range(“A:C”)
.Header = xlYes
.Orientation = xlTopToBottom
End With

End Sub

The code to populate the ComboBox with unique items as shown earlier is also reproduced below:

Private Sub ComboBox1_Change()
Label1.Caption = “Total ” & ComboBox1.Value & ” available”
Label2.Caption = WorksheetFunction.SumIf(Columns(1), ComboBox1.Value, Columns(2)) – WorksheetFunction.SumIf(Columns(1), ComboBox1.Value, Columns(3))

End Sub

Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim myCollection As Collection, cell As Range
On Error Resume Next
Set myCollection = New Collection
With ComboBox1
For Each cell In Range(“A2:A” & Cells(Rows.Count, 1).End(xlUp).Row)
If Len(cell) <> 0 Then
myCollection.Add cell.Value, cell.Value
If Err.Number = 0 Then .AddItem cell.Value
End If
Next cell
End With
ComboBox1.ListIndex = 0
End Sub

Watch the Excel training video:

Further reading:
Sort Method [Excel 2003 VBA Language Reference]

2 thoughts on “Sort ComboBox Data Using VBA

  1. need to do sort the list box item by typing the items in the test above and the selection of the items count. pls help

  2. Need to do the sorting the list box items by typing the list items in the text box above the list box and the selection count should obtain what is items selected….Pls help

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.