April 10, 2017

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

End Sub

Private Sub ComboBox1_Change()

FilterCbo1 (UCase(ComboBox1.Value))

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
End Sub

Private Sub CommandButton1_Click()
End Sub
Private Sub UserForm_Initialize()
FilterCbo1 “y”
End Sub

Further reading:

  1. INSTR Function
  2. Excel Drop Down Lists

[amazon_link asins=’1119067723′ template=’ProductAd’ store=’freeexceltrai-20′ marketplace=’US’ link_id=’9075f9f3-25ad-11e7-bf56-57f53b00379a’]

3 thoughts on “Filter Data in Combo Box

  1. Respected sir,

    I have gone through the videos you have uploaded & i feel that it may solve out my problem too.

    Kindly help me with one module commands through which i can open all the details of a single worksheet (example:details of one among my 100 my customers), from the first page, simply entering the sheet name or ID number of that particularcustomer in the first page in one click.

    Afjal Dewan

  2. Dear Sir,

    Thanks for your video.

    I get a lot of ideas from your lessons and think this may be useful, but I cannot get it to work the same way. The combobox add items contains all strings with the letter selected and does not appear to be looking only at the first character. I have checked my code over and over.

  3. Sir,

    I Need your support ,
    i am preparing a user form to enter & search data from a log sheet
    i need 2 comboboxes which reflects values as
    Combobox1 = Column A unique values by avoiding duplicates
    combobox2= it will populate based on the selection in Combobox1
    the values could be more than 1
    my script with combox1 is working perfectly it shows me the unique values
    but cant proceed further to get information in combobox 2
    kindly i know you can help me out , and i wana share you my sheet

Comments are closed.