none
Autocomplete / Filter ComboBox in Userform RRS feed

  • Question

  • Hi, 

    I have a userform with several comboboxes with a very few list options, but one of the combox is a bit long to scroll and select.

    The built-in match property isnt usefull because i cant remember first letter of all list items.

    I want a userform combobox that filters items based on ANY letters entered in combobox, not just first letters.

    So far I only found solution for combobox in excel table, but i couldnt make it work on a userform.

    All the list items are stored in a column in a sheet (along with other list for other comboboxes).

    I suppose I need some code that works on combobox selection change event.

    Big thanks.

    Monday, September 2, 2019 6:13 PM

Answers

  • Hi,
    I suggest way to use the KeyUp event.
    this way can also be used with Word and others.

    Option Explicit
     
    Private WithEvents cboAutoComplete As MSForms.ComboBox
    Private cboStored As Object
     
    Private Sub UserForm_Initialize()
      Dim v As Variant
      Dim i As Long
      
      'add list items to ComboBox
      v = Array("Mansfield-et-Pontefract", "Cognelee", "Gujrat", "Jaboatao dos Guararapes", _
                "Oberhausen", "La Rochelle", "Linkebeek", "Peterhead", "Lombardsijde", "Stratford", _
                "Manukau", "Jasper", "Guntakal", "Voitsberg", "Rocca di Cambio", "Merchtem", "Monchengladbach", _
                "Cognelee", "Jhang", "Neder-Over-Heembeek", "Builth Wells", "Cherbourg-Octeville", _
                "Varsenare", "Melipilla", "Villers-Perwin", "Tullibody", "Bollnas", "Pescantina", _
                "Frederick", "Orosei")
      With ComboBox1
        .MatchEntry = fmMatchEntryNone
        For i = LBound(v) To UBound(v)
          .AddItem v(i)
        Next
      End With
      
      'copy ComboBox list to another ComboBox
      Set cboAutoComplete = ComboBox1
      Set cboStored = CreateObject("Forms.ComboBox.1")
      cboStored.List = cboAutoComplete.List
    End Sub
    
    Private Sub cboAutoComplete_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      Dim accCbo As Office.IAccessible
      Dim accLst As Office.IAccessible
      Dim i As Long
       
      Select Case KeyCode
        Case vbKeyBack, vbKeySpace, vbKeyDelete, _
             vbKeyA To vbKeyZ, vbKey0 To vbKey9, _
             vbKeyNumpad0 To vbKeyNumpad9
          
          'filtering ComboBox
          cboAutoComplete.Clear
          For i = 0 To cboStored.ListCount - 1
            If LCase(cboStored.List(i)) Like "*" & LCase(cboAutoComplete.Text) & "*" Then
              cboAutoComplete.AddItem cboStored.List(i)
            End If
          Next
          
          'close dropdown
          Set accCbo = cboAutoComplete
          If accCbo.accName(&H2&) = "Close" Then
            Set accLst = accCbo.accChild(&H3&)
            accLst.accDoDefaultAction &H0&
            DoEvents
          End If
          
          cboAutoComplete.DropDown
      End Select
    End Sub



    Best Regards,
    kinuasa
    • Marked as answer by mkFusion Thursday, September 5, 2019 7:15 PM
    Thursday, September 5, 2019 1:29 AM

All replies

  • Hi,
    Which application are you using for your userform, Excel, Access, or other?

    Regards,

    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Tuesday, September 3, 2019 2:03 AM
  • I have excel table and button that triggers userform for more convenient rows processing.

    One sheet contains the database itself, other sheet contains static ranges for combobox sources.

    One range is kinda long, nearly 800-900 items, and I want to add little functionality to that combobox, in order to avoid long scrolling when searching the list item. 

    Again, I want to extend the built-in match functionality of generic combobox, and match entered string anywhere in list item, not just beginning.

    Thanks.

    Tuesday, September 3, 2019 2:45 PM
  • I would do this differently.  Excel can connect to a Named Range and you can use SQL to query Named Range as a table. Refer to these tutorials (71-92).

    Make your combo list a named range (ComboList).  Make column heading ComboItem.

     Have a textbox and a listbox.  As you type in textbox it will query table and populate list box.  You'll probably want to limit items returned or only query after you have entered a few characters.  SQL will look like this:

    "SELECT TOP 20 ComboItem FROM ComboList WHERE ComboItem LIKE '*" & TextBoxVal & "*'"

    TextBoxVal is value of textbox

    Tuesday, September 3, 2019 4:10 PM
  • Thanks for reply, but that sounds a bit advanced for me, and i want to make it simple with only one control.

    I had a simple solution in my head, something like:

    for each cell in named sheet range

    if inputed value in combobox contains in cell value, add that item in combobox

    However, tutorials seem good, so i might check the referred lessons

    Tuesday, September 3, 2019 9:15 PM
  • Hi,

    I made a sample VBA.

    [code in Sheet1]

    Public LastRow1 As Integer
    ' ---
    Private Sub btn_ShowForm_Click()
        With UserForm1
            ' --- initialize
            .TextBox1.Text = ""
            .ComboBox1.Clear
            .ComboBox2.Clear
            Call ComboBox1_Fill_All ' -- Add Items from WorkSheet
            Call ComboBox2_Fill_All
            .Show   ' -- Show UserForm
        End With
    End Sub
    ' ---
    Public Sub ComboBox1_Fill_All()
        With UserForm1
            Dim r As Integer
            ' --- add item
            LastRow1 = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
            For r = 2 To LastRow1    ' -- row 1 is header
                .ComboBox1.AddItem Sheets(1).Cells(r, 1).Value
            Next
        End With
    End Sub
    ' ---
    Private Sub ComboBox2_Fill_All()
        With UserForm1
            Dim r As Integer
            Dim lastrow2 As Integer
            ' --- for ComboBox2
            lastrow2 = Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
            For r = 2 To lastrow2
                .ComboBox2.AddItem Sheets(1).Cells(r, 2).Value
            Next
        End With
    End Sub
    

    [code in UserForm1]

    Private Sub TextBox1_Change()
        Dim kw As String    ' -- keyword for filtering
        Dim r As Integer    ' -- row index
        ' ---
        ComboBox1.Clear
        ' ---
        If (Len(TextBox1.Text) = 0) Then
            Call Sheets(1).ComboBox1_Fill_All
        Else
            kw = TextBox1.Text
            For r = 2 To Sheets(1).LastRow1
                If (InStr(Cells(r, 1).Value, kw) > 0) Then
                    UserForm1.ComboBox1.AddItem Cells(r, 1).Value
                End If
            Next
        End If
    End Sub

    ** note **
    (1) "ComboBox1" is to be filtered with text in TextBox1.
    (2) letters used as filter are case sensitive.
    (3) items for ComboBox are in column A and  B on one worksheet.

    Regards,


    Ashidacchi -- https://ssl01.rocketnet.jp/hokusosha.com/default.html

    Thursday, September 5, 2019 12:36 AM
  • Hi,
    I suggest way to use the KeyUp event.
    this way can also be used with Word and others.

    Option Explicit
     
    Private WithEvents cboAutoComplete As MSForms.ComboBox
    Private cboStored As Object
     
    Private Sub UserForm_Initialize()
      Dim v As Variant
      Dim i As Long
      
      'add list items to ComboBox
      v = Array("Mansfield-et-Pontefract", "Cognelee", "Gujrat", "Jaboatao dos Guararapes", _
                "Oberhausen", "La Rochelle", "Linkebeek", "Peterhead", "Lombardsijde", "Stratford", _
                "Manukau", "Jasper", "Guntakal", "Voitsberg", "Rocca di Cambio", "Merchtem", "Monchengladbach", _
                "Cognelee", "Jhang", "Neder-Over-Heembeek", "Builth Wells", "Cherbourg-Octeville", _
                "Varsenare", "Melipilla", "Villers-Perwin", "Tullibody", "Bollnas", "Pescantina", _
                "Frederick", "Orosei")
      With ComboBox1
        .MatchEntry = fmMatchEntryNone
        For i = LBound(v) To UBound(v)
          .AddItem v(i)
        Next
      End With
      
      'copy ComboBox list to another ComboBox
      Set cboAutoComplete = ComboBox1
      Set cboStored = CreateObject("Forms.ComboBox.1")
      cboStored.List = cboAutoComplete.List
    End Sub
    
    Private Sub cboAutoComplete_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
      Dim accCbo As Office.IAccessible
      Dim accLst As Office.IAccessible
      Dim i As Long
       
      Select Case KeyCode
        Case vbKeyBack, vbKeySpace, vbKeyDelete, _
             vbKeyA To vbKeyZ, vbKey0 To vbKey9, _
             vbKeyNumpad0 To vbKeyNumpad9
          
          'filtering ComboBox
          cboAutoComplete.Clear
          For i = 0 To cboStored.ListCount - 1
            If LCase(cboStored.List(i)) Like "*" & LCase(cboAutoComplete.Text) & "*" Then
              cboAutoComplete.AddItem cboStored.List(i)
            End If
          Next
          
          'close dropdown
          Set accCbo = cboAutoComplete
          If accCbo.accName(&H2&) = "Close" Then
            Set accLst = accCbo.accChild(&H3&)
            accLst.accDoDefaultAction &H0&
            DoEvents
          End If
          
          cboAutoComplete.DropDown
      End Select
    End Sub



    Best Regards,
    kinuasa
    • Marked as answer by mkFusion Thursday, September 5, 2019 7:15 PM
    Thursday, September 5, 2019 1:29 AM
  • Ashidacchi big thanks for the answer, but i was looking instant filter on the very same control.

    However, i like the code because it teached me some vba tricks, so thanks again :)

    Thursday, September 5, 2019 7:09 PM
  • kinuasa, from what I see from provided gif, thats perfectly what i was looking for!

    I will implement your code this weekend

    Thanks !

    Thursday, September 5, 2019 7:15 PM