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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

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.

    Thankfully-
    Afjal Dewan
    Assam.

  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,
    Greetings

    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

Leave a Reply

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