Filter Data in Combo Box

How to filter data in a combo box to see only the values we need.  First we create a custom or user defined function using the inbuilt INSTR function.

Watch the video:

 

Watch the video on YouTube.

Here’s the complete VBA code:

The custom function code:

Public Sub FilterCbo1(strFilter As String)

Dim lastrow As Long
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
For Each strchoice In Range(Cells(2, 1), Cells(lastrow, 1))
If InStr(1, strchoice, strFilter) <> 0 Then
UserForm1.ComboBox1.AddItem strchoice
End If
Next

End Sub

Private Sub ComboBox1_Change()

FilterCbo1 (UCase(ComboBox1.Value))
ComboBox1.DropDown

End Sub

Private Sub ComboBox1_Click()
TextBox1.Value = ComboBox1.Value
Dim i, lastrow As Long
lastrow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row

For i = 2 To lastrow
If Cells(i, 1) = ComboBox1.Value Then
Cells(i, 2) = ComboBox1.Value
MsgBox “Data found in cell address ” & Cells(i, 2).Address
End If
Next
End Sub

Private Sub CommandButton1_Click()
UserForm1.ComboBox1.Clear
UserForm1.ComboBox1.Clear
End Sub
Private Sub UserForm_Initialize()
FilterCbo1 “y”
Me.ComboBox1.Clear
End Sub

Further reading:

  1. INSTR Function
  2. Excel Drop Down Lists