How to Filter ComboBox Data in Excel VBA

A ComboBox is a strong tool that allows users to select from a list of predefined items. However, if the list is too big, It may become difficult for the user to find the required item. We can filter the items in the ComboBox according to different criteria by using VBA in Excel. In this article, we will discuss how to filter ComboBox data in Excel VBA.

How to Open the VBA Macro Editor in Excel

You need to follow the steps below to get into the VBA Macro Editor in your worksheet:

• You will see the Developer tab at the top of the worksheet. Click on the Developer tab and select Visual Basic. Alternatively, you can press Alt+F11.

• A new window will appear. It is the Visual Basic Editor window. To write new code, go to Insert >> Module.

• In the module, write the code and click on the Run button to run the code. You can also press the F5 key to run the code.

4 Methods to Create ComboBox and Filter Data in Excel VBA

In this article, we will demonstrate 4 methods to create ComboBox and filter data in Excel VBA. In the dataset, we have the academic records of ten students. The dataset includes their names, departments, and CGPAs. We will create a ComboBox that will show the names of the students. Then, we will filter the ComboBox in such a way that it will show only those students who have a CGPA greater than 3.5.

1. Create ComboBox from ActiveX Controls and Filter Data in Excel VBA 

We can create a ComboBox from ActiveX Controls feature in Excel VBA. ActiveX Controls are pre-built user interface elements that can be easily added to an Excel worksheet. We will create a ComboBox from ActiveX Controls that will show the names of the students. Then we will filter the data in the ComboBox in such a way that it will show only those students who have a CGPA over 3.5.

Step 1: Create ActiveX Controls ComboBox to Filter Data in Excel VBA

Simply follow the steps below to create a ComboBox from ActiveX Controls:

• Go to the Developer tab >> Insert >> Combo Box (ActiveX Controls).

• Create a ComboBox from the plus icon.

• Right-Click on the ComboBox and select Properties.

• Set the ListFillRange property to the range of cells that you want to show in the ComboBox dropdown list. We have set the range as ActiveX_Controls_ComboBox!B5:B14.

Note: Similarly, you can put a named range or a Table name here instead of a range of cells.

• Turn off the Design Mode from the Developer tab.

• Now, you will see the dropdown list showing all the names from B5:B14 in the ComboBox.

Step 2: Filter Data from ActiveX Controls ComboBox in Excel VBA

We will use the following VBA code to filter data in the ActiveX Controls ComboBox.

To do so follow the steps below:

• Turn on the Design Mode and clear the ListFillRange Property of the ComboBox.

• Double-click on the ComboBox. A Window will pop up. Enter the following code into that window:

Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    'variable declaration
    Dim myRng As Range
    Dim myStr As String
    Set myRng = Sheets("ActiveX_Controls_ComboBox").Range("B5:D14")
    'filter values
    For i = 1 To myRng.Rows.Count
        If myRng.Cells(i, 3) > 3.5 Then
            myStr = myStr & myRng.Cells(i, 1) & ","
        End If
    Next i
    'put the filtered values in ComboBox
    ComboBox1.List = Split(Left(myStr, Len(myStr) - 1), ",")
End Sub

• Return to the main sheet, and turn off the Design Mood.

• Double-click on the ComboBox to activate the code.

• Now you will see the dropdown list of the filtered data (the students with a CGPA of more than 3.5) in the ComboBox.

Private Sub ComboBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

    Dim myRng As Range

    Dim myStr As String

    Set myRng = Sheets("ActiveX_Controls_ComboBox").Range("B5:D14")

ActiveX_Controls_ComboBox.
    For i = 1 To myRng.Rows.Count
        If myRng.Cells(i, 3) > 3.5 Then
            myStr = myStr & myRng.Cells(i, 1) & ","
        End If
    Next i

    ComboBox1.List = Split(Left(myStr, Len(myStr) - 1), ",")
End Sub

2. Create ComboBox as Shape and Filter Data in Excel VBA 

In this method, we will create a ComboBox from Excel VBA. We will create a ComboBox shape in VBA and add the student names to the dropdown list of the ComboBox. Then we will modify the VBA code to filter the dropdown list so that we can see only those students who have CGPAs of more than 3.5 in the ComboBox.

Step1: Create Shape ComboBox to Filter Data in Excel VBA

We will use this code to create a ComboBox from Excel VBA.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Shape_ComboBox()
    'variable declaration
    Dim ws As Worksheet
    Dim myRng As Range
    Set ws = ThisWorkbook.Worksheets("Shape_ComboBox")
    'Create a new ComboBox shape
    Dim cb As Shape
    Set cb = ws.Shapes.AddFormControl _
    (xlDropDown, Left:=ws.Range("E4").Left, Top:=ws.Range("E4").Top, Width:=60, Height:=20)
    'Set the properties of the ComboBox
    With cb.OLEFormat.Object
    'Set the data source range
        .ListFillRange = "Shape_ComboBox!B5:B14"
    'Set the number of visible items in the dropdown
        .DropDownLines = 10
    End With
End Sub

Sub Shape_ComboBox()

Shape_ComboBox.

    Dim ws As Worksheet
    Dim myRng As Range
    Set ws = ThisWorkbook.Worksheets("Shape_ComboBox")

    Dim cb As Shape
    Set cb = ws.Shapes.AddFormControl _
    (xlDropDown, Left:=ws.Range("E4").Left, Top:=ws.Range("E4").Top, Width:=60, Height:=20)

    With cb.OLEFormat.Object
        .ListFillRange = "Shape_ComboBox!B5:B14"
        .DropDownLines = 10
    End With
End Sub

Step2: Filter Data from Shape ComboBox in Excel VBA

We will use this VBA code to filter data in the ComboBox. We have modified the previous code here.

Enter the following code in your VBA Editor and press the Run button or F5 key to run the code:

Sub Shape_ComboBox_Filter()
    'variable declaration
    Dim ws As Worksheet
    Dim myRng As Range
    Dim myArr() As Variant
    Dim Count As Integer
    Set ws = ThisWorkbook.Worksheets("Shape_ComboBox")
    Set myRng = Sheets("Shape_ComboBox").Range("B5:D14")
    'filter values
    For i = 1 To myRng.Rows.Count
        If myRng.Cells(i, 3) > 3.5 Then
            ReDim Preserve myArr(Count)
            myArr(Count) = myRng.Cells(i, 1)
            Count = Count + 1
        End If
    Next i
    'Create a new ComboBox shape
    Dim cb As Shape
    Set cb = ws.Shapes.AddFormControl _
    (xlDropDown, Left:=ws.Range("E4").Left, Top:=ws.Range("E4").Top, Width:=60, Height:=20)
    'Set the properties of the ComboBox
    With cb.OLEFormat.Object
    'Set the data source range
        .List = myArr
    'Set the number of visible items in the dropdown
        .DropDownLines = 5
    End With
End Sub

Sub Shape_ComboBox_Filter()

    Dim ws As Worksheet
    Dim myRng As Range
    Dim myArr() As Variant
    Dim Count As Integer
    Set ws = ThisWorkbook.Worksheets("Shape_ComboBox")
    Set myRng = Sheets("Shape_ComboBox").Range("B5:D14")

    For i = 1 To myRng.Rows.Count
        If myRng.Cells(i, 3) > 3.5 Then
            ReDim Preserve myArr(Count)
            myArr(Count) = myRng.Cells(i, 1)
            Count = Count + 1
        End If
    Next i

    Dim cb As Shape
    Set cb = ws.Shapes.AddFormControl _
    (xlDropDown, Left:=ws.Range("E4").Left, Top:=ws.Range("E4").Top, Width:=60, Height:=20)
    With cb.OLEFormat.Object
        .List = myArr
        .DropDownLines = 5
    End With
End Sub

3. Create ComboBox in a UserForm and Filter Data in Excel VBA

We can easily create a ComboBox in a UserForm. ComboBox is available as a control element in the Toolbox of a UserForm. We will add a ComboBox with the list of student names. Then we will filter out the list and add it back to the UserForm by a VBA code.

Step1: Create ComboBox in a UserForm to Filter Data in Excel VBA

Just follow the steps below to create a UserForm with a ComboBox in Excel VBA:

• Go to Insert >> UserForm.

• Create a UserForm. Select a ComboBox from the Toolbox.

• You can change the properties of the ComboBox from the Properties Window (press F4 to view the window). For example, we have set the RowSource property to UserForm_ComboBox!B5:B14. So, the ComboBox will show values from cells B5:B14 from the worksheet UserForm_ComboBox.

• Now, run the UserForm to see the results.

Step2: Filter Data from UserForm ComboBox in Excel VBA

We will use this code to filter data in the ComboBox in UserForm.

Simply follow these steps:

• Clear the RowSource property if any other range of cells is specified there.

• Double-click on the UserForm. A new window will open. Enter the following code in the space:

Private Sub UserForm_Initialize()
    'variable declaration
    Dim myRng As Range
    Set myRng = Sheets("UserForm_ComboBox").Range("B5:D14")
    'filter values
    For i = 1 To myRng.Rows.Count
        If myRng.Cells(i, 3) > 3.5 Then
            Me.ComboBox1.AddItem myRng.Cells(i, 1)
        End If
    Next i
End Sub

• Run the code to see the filtered data in the

  UserForm ComboBox

  .

VBA Breakdown

Private Sub UserForm_Initialize()

The first line of the code is a Sub procedure that is triggered when the UserForm is initialized, i.e., when it is opened.

    Dim myRng As Range
    Set myRng = Sheets("UserForm_ComboBox").Range("B5:D14")

It creates a Range object named myRng that represents a range of cells (B5:D14) in the worksheet UserForm_ComboBox.   

    For i = 1 To myRng.Rows.Count
        If myRng.Cells(i, 3) > 3.5 Then
            Me.ComboBox1.AddItem myRng.Cells(i, 1)
        End If
    Next i
End Sub

The For loop then iterates through each row in the range myRng and checks if the value in the third column of that row is greater than 3.5. If the value is greater than 3.5, the code adds the corresponding value in the first column of that row to the ComboBox using the AddItem method. The Me keyword refers to the UserForm that contains the ComboBox control. Once the loop has finished, the ComboBox will contain only those items from the first column of the myRng range where the corresponding value in the third column is greater than 3.5.