How to Filter Combo Box as User Typed?

R

riyajugen

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have using one of ComboBox in UserForm and load the values from code behine function called while Userform Iniated. I need that ComboBox should filter and show as what user typed.

Eg:

Values are "OUTLET, SALES, SUPPLIER, SALES CATEGORY, PURCHASE, SALES MODE"
User type as "SA".. list items should displays "SALES, SALES CATEGORY, SALES MODE"
If values are empty, ComboBox should display with all items as beginning.

Coding as Follows:
VBA Code: 
Private Sub UserForm_Initialize()
    CategoryListLoad
End Sub

Sub CategoryListLoad()
    On Error Resume Next
    ddlCategoryName.Clear
    ddlCategoryName.AddItem ("OUTLET")
    ddlCategoryName.AddItem ("SAPPLIER")
    ddlCategoryName.AddItem ("SALES CATEGORY")
    ddlCategoryName.AddItem ("PAYMENT MODE")
    ddlCategoryName.AddItem ("EMPLOYEES")
    ddlCategoryName.AddItem ("SERVICE PROVIDERS")
    ddlCategoryName.AddItem ("GENERAL EXPENSES")
    ddlCategoryName.AddItem ("MISCELLANEOUS EXPENSES")
    ddlCategoryName.AddItem ("VEHICLE TYPE")
    ddlCategoryName.AddItem "SELECT YOUR CATEGORY", 0
    ddlCategoryName.ListIndex = 0
    ddlCategoryName.SetFocus
End Sub

Private Sub ddlCategoryName_Change()
'Serach Filter function here.

End Sub
 

Attachments

  • Temp Userform.jpg
    Temp Userform.jpg
    49 KB · Views: 13

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hello Riyajugen,
you need to create collection that will keep your list.
Clear combobox and fill it with items from collection that meet search parametar from text box.
Add one text box and change him location out of userform size.
This textbox uses to temporary remove focus from combobox during search.

VBA Code: 
Dim varNLoops As Integer
Dim varCollection As New Collection

Private Sub UserForm_Initialize()
   
   CategoryListLoad
   ddlCategoryName.MatchEntry = fmMatchEntryNone
  
End Sub

Private Sub ddlCategoryName_Change()
   
    If ddlCategoryName.ListIndex > -1 Then _
        MsgBox ddlCategoryName.List(ddlCategoryName.ListIndex)
    TextBox1.SetFocus
    ddlCategoryName.Clear
    For varNLoops = 1 To varCollection.Count
        If InStr(1, UCase(varCollection.Item(varNLoops)), _
                    UCase(ddlCategoryName.Text)) Then
            ddlCategoryName.AddItem varCollection.Item(varNLoops)
        End If
    Next
    ddlCategoryName.SetFocus
    ddlCategoryName.DropDown
   
End Sub

Sub CategoryListLoad()

    With varCollection
           .Add "OUTLET"
           .Add "SAPPLIER"
           .Add "SALES CATEGORY"
           .Add "PAYMENT MODE"
           .Add "EMPLOYEES"
           .Add "SERVICE PROVIDERS"
           .Add "GENERAL EXPENSES"
           .Add "MISCELLANEOUS EXPENSES"
           .Add "VEHICLE TYPE"
           .Add "SELECT YOUR CATEGORY"
    End With
    For varNLoops = 1 To varCollection.Count
        ddlCategoryName.AddItem varCollection(varNLoops)
    Next
    ddlCategoryName.Text = ""
    ddlCategoryName.SetFocus

End Sub
 
Upvote 0
Hi

It's working, but dropdown items are multiples (duplicates) each time key entered. (pls refer image)
 

Attachments

  • Temp Userform.jpg
    Temp Userform.jpg
    75 KB · Views: 15
Upvote 0
Hi @riyajugen
Here's an example of a searchable combobox in Userform:
www.mediafire.com

deCombobox,userform,seachable,filter

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.
www.mediafire.com

NOTE:
  1. You need to put the list for the combobox in a sheet (e.g Sheet2).
  2. You can use one or more keywords separated by a space, like: ha ca
  3. The list will be narrowed down as you type
  4. Use down/up arrow to navigate
  5. Hit enter to select

The code:

VBA Code: 
Option Explicit
'=============== YOU NEED TO ADJUST THE CODE IN THIS PART: ===================================

'sheet's name where the list (for combobox) is located. [in the sample: sheet "sheet2"]
Private Const sList As String = "Sheet2"

'cell where the list start [in the sample: cell A2 in sheet "sheet2" ]
Private Const sCell As String = "A2"
'===================================================================================================

Private vList


Private Sub ComboBox1_Enter()

With Sheets(sList)
    vList = .Range(sCell, .Cells(.Rows.Count, .Range(sCell).Column).End(xlUp)).Value
End With
    ComboBox1.MatchEntry = fmMatchEntryNone
    ComboBox1.Value = ""
    ComboBox1.List = vList
End Sub

Private Sub ComboBox1_Change()
Dim z, ary

With ComboBox1
    If .Value <> "" And IsError(Application.Match(.Value, vList, 0)) Then
            ary = Application.Transpose(vList)
                
                For Each z In Split(.Value, " ")
                    ary = Filter(ary, z, True, vbTextCompare)
                Next
           
           .List = ary
           .DropDown
           
    ElseIf .Value = "" Then
        .List = vList
        
    End If
End With
End Sub
 
Upvote 0
I don't understand this about duplicates,
you are adding items manually, remove duplicates before adding items.
Code work exactly as you ask in the first post,
but now I see you want visible list all time.
You can use this changed code...

VBA Code: 
'this goes in the standard module as separate procedure
Sub ShowList()

    UserForm1.ddlCategoryName.DropDown
    
End Sub


'this goes in the Userform1 module
Dim varNLoops As Integer
Dim varCollection As New Collection

Private Sub UserForm_Initialize()
    
   CategoryListLoad
   ddlCategoryName.MatchEntry = fmMatchEntryNone
   Application.OnTime Now + TimeValue("00:00:01"), "ShowList"
   
End Sub

Private Sub ddlCategoryName_Change()
    
    If ddlCategoryName.ListIndex > -1 Then _
        MsgBox ddlCategoryName.List(ddlCategoryName.ListIndex)
    TextBox1.SetFocus
    ddlCategoryName.Clear
    For varNLoops = 1 To varCollection.Count
        If InStr(1, UCase(varCollection.Item(varNLoops)), _
                    UCase(ddlCategoryName.Text)) Then
            ddlCategoryName.AddItem varCollection.Item(varNLoops)
        End If
    Next
    ddlCategoryName.SetFocus
    ddlCategoryName.DropDown
    
End Sub

Sub CategoryListLoad()

    With varCollection
           .Add "OUTLET"
           .Add "SALES"
           .Add "SUPPLIER"
           .Add "SALES MODE"
           .Add "SALES CATEGORY"
           .Add "SERVICE PROVIDERS"
           .Add "GENERAL EXPENSES"
           .Add "MISCELLANEOUS EXPENSES"
           .Add "VEHICLE TYPE"
           .Add "SELECT YOUR CATEGORY"
    End With
    For varNLoops = 1 To varCollection.Count
        ddlCategoryName.AddItem varCollection(varNLoops)
    Next
    ddlCategoryName.Text = ""
    ddlCategoryName.SetFocus

End Sub
 
Upvote 0
Solution
EXCEL MAX said:
I don't understand this about duplicates,
you are adding items manually, remove duplicates before adding items.
Code work exactly as you ask in the first post,
but now I see you want visible list all time.
You can use this changed code...

VBA Code: 
'this goes in the standard module as separate procedure
Sub ShowList()

    UserForm1.ddlCategoryName.DropDown
   
End Sub


'this goes in the Userform1 module
Dim varNLoops As Integer
Dim varCollection As New Collection

Private Sub UserForm_Initialize()
   
   CategoryListLoad
   ddlCategoryName.MatchEntry = fmMatchEntryNone
   Application.OnTime Now + TimeValue("00:00:01"), "ShowList"
  
End Sub

Private Sub ddlCategoryName_Change()
   
    If ddlCategoryName.ListIndex > -1 Then _
        MsgBox ddlCategoryName.List(ddlCategoryName.ListIndex)
    TextBox1.SetFocus
    ddlCategoryName.Clear
    For varNLoops = 1 To varCollection.Count
        If InStr(1, UCase(varCollection.Item(varNLoops)), _
                    UCase(ddlCategoryName.Text)) Then
            ddlCategoryName.AddItem varCollection.Item(varNLoops)
        End If
    Next
    ddlCategoryName.SetFocus
    ddlCategoryName.DropDown
   
End Sub

Sub CategoryListLoad()

    With varCollection
           .Add "OUTLET"
           .Add "SALES"
           .Add "SUPPLIER"
           .Add "SALES MODE"
           .Add "SALES CATEGORY"
           .Add "SERVICE PROVIDERS"
           .Add "GENERAL EXPENSES"
           .Add "MISCELLANEOUS EXPENSES"
           .Add "VEHICLE TYPE"
           .Add "SELECT YOUR CATEGORY"
    End With
    For varNLoops = 1 To varCollection.Count
        ddlCategoryName.AddItem varCollection(varNLoops)
    Next
    ddlCategoryName.Text = ""
    ddlCategoryName.SetFocus

End Sub
Click to expand...
Here Duplicates means. The dropdown list item multiple times added each time doing search function.
Initially list count 7 Items, after search anything list count are increased 14, later it's 21 after another search occurred.

How do i prevent that multiple times list increasing while each search operation,
 

Attachments

  • Pre Find.jpg
    Pre Find.jpg
    77.3 KB · Views: 6
  • On Find.jpg
    On Find.jpg
    59.9 KB · Views: 6
  • After Find.jpg
    After Find.jpg
    77.9 KB · Views: 6
Upvote 0
Akuini said:
Hi @riyajugen
Here's an example of a searchable combobox in Userform:
www.mediafire.com

deCombobox,userform,seachable,filter

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.
www.mediafire.com

NOTE:
  1. You need to put the list for the combobox in a sheet (e.g Sheet2).
  2. You can use one or more keywords separated by a space, like: ha ca
  3. The list will be narrowed down as you type
  4. Use down/up arrow to navigate
  5. Hit enter to select

The code:

VBA Code: 
Option Explicit
'=============== YOU NEED TO ADJUST THE CODE IN THIS PART: ===================================

'sheet's name where the list (for combobox) is located. [in the sample: sheet "sheet2"]
Private Const sList As String = "Sheet2"

'cell where the list start [in the sample: cell A2 in sheet "sheet2" ]
Private Const sCell As String = "A2"
'===================================================================================================

Private vList


Private Sub ComboBox1_Enter()

With Sheets(sList)
    vList = .Range(sCell, .Cells(.Rows.Count, .Range(sCell).Column).End(xlUp)).Value
End With
    ComboBox1.MatchEntry = fmMatchEntryNone
    ComboBox1.Value = ""
    ComboBox1.List = vList
End Sub

Private Sub ComboBox1_Change()
Dim z, ary

With ComboBox1
    If .Value <> "" And IsError(Application.Match(.Value, vList, 0)) Then
            ary = Application.Transpose(vList)
               
                For Each z In Split(.Value, " ")
                    ary = Filter(ary, z, True, vbTextCompare)
                Next
          
           .List = ary
           .DropDown
          
    ElseIf .Value = "" Then
        .List = vList
       
    End If
End With
End Sub
Click to expand...
Hi,

ComboBox values does not from sheet and cells, instead added from code behind.
 
Upvote 0
Post your code from "Private Sub ddlCategoryName_Change()".
 
Upvote 0
EXCEL MAX said:
Post your code from "Private Sub ddlCategoryName_Change()".
Click to expand...
VBA Code: 
If ddlCategoryName.ListIndex > -1 Then _
        MsgBox ddlCategoryName.List(ddlCategoryName.ListIndex)
    TextBox1.SetFocus
    ddlCategoryName.Clear
    For varNLoops = 1 To varCollection.Count
        If InStr(1, UCase(varCollection.Item(varNLoops)), _
                    UCase(ddlCategoryName.Text)) Then
            ddlCategoryName.AddItem varCollection.Item(varNLoops)
        End If
    Next
    ddlCategoryName.SetFocus
    ddlCategoryName.DropDown
 
Upvote 0
This is OK.
Is that all?
Maybe you calling procedure "Subcategory List Load()" after last line of this code or some event activate this procedure.
Go to the debugger and step by step see when the list duplicated.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
Listbox Filter using a combobox
Replies
1
Views
3K
DanteAmor
DanteAmor
T
  • Solved
When _Change() dynamically clears and re-adds items to ComboBox, box remains null when selecting item
Replies
8
Views
881
T1E1
T
N
  • Question
Add the value taken from the user to my range for ActiveX ComboBox
Replies
1
Views
481
Peter_SSs
Peter_SSs
M
  • Question
Filtering data in listbox using two combo boxes
Replies
1
Views
717
matix003
M
S
  • Solved
How to use a ComboBox with autocomplete and search as you type, but ignore blank formula or empty cells.
2
Replies
10
Views
4K
sijpie
sijpie

Forum statistics

Threads
1,214,646
Messages
6,120,717
Members
448,985
Latest member
chocbudda

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top