Sort Excel Worksheet Data and ComboBox Data Using VBA

     Webhosting by Godaddy     MS Excel Books
Home More Excel (VBA) Training Videos

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:


Watch the video on YouTube

Sort Data in Excel Worksheet Using VBA

Home More Excel (VBA) Training Videos