How to Filter Combo Box as User Typed?

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: 7

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
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
 

riyajugen

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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: 5

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,963
Office Version
  1. 365
Platform
  1. Windows
Hi @riyajugen
Here's an example of a searchable combobox in Userform:

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
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 
Solution

riyajugen

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
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: 3
  • On Find.jpg
    On Find.jpg
    59.9 KB · Views: 3
  • After Find.jpg
    After Find.jpg
    77.9 KB · Views: 3

riyajugen

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi @riyajugen
Here's an example of a searchable combobox in Userform:

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

ComboBox values does not from sheet and cells, instead added from code behind.
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
Post your code from "Private Sub ddlCategoryName_Change()".
 

riyajugen

New Member
Joined
Nov 10, 2020
Messages
9
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Post your code from "Private Sub ddlCategoryName_Change()".
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
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,304
Messages
5,635,417
Members
416,857
Latest member
m2pk

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
Top