Search code examples
excelvbauserform

Partial String Match (How to match any part of a string while I type in a combobox?)

What I have:

[Form: "Intajform"] - [Combobox: "CustomerName_Combobox"] - [Initialize Event: Load list]

Private Sub UserForm_Initialize()

   Dim ws As Worksheet, rCell, srr As Range, Key
   Dim Dic As Object: Set Dic = CreateObject("Scripting.Dictionary")

   Set ws = ThisWorkbook.Worksheets("Backend")
   Set srr = ws.Range("b2", ws.Cells(Rows.Count, "b").End(xlUp))

   For Each rCell In srr
      If Not Dic.exists(rCell.Value) Then
         Dic.Add (rCell.Value), Nothing
      End If
   Next rCell

   For Each Key In Dic
      IntajForm.CustomerName_Combobox.AddItem Key
   Next

End Sub

While typing in that combobox to find a match

I CAN FULLY MATCH STRINGS BY FIRST LETTER ONLY EX: If I type "M" then I find "Microsoft Corporation"

BUT I CAN NOT PARTIALLY MATCH STRINGS EX: If I type "r" then it is blank " "

Target

I want to partially match the string found in Combobox as long as the letter I type is found in the string.

Solution

  • Use the comboboxe's _Change() event

    You have only reduced possibilities to define the .MatchEntry property of a combobox - cf. MS Help - MatchEntry property:

    • .MatchEntry = fmMatchEntryFirstLetter '
    • .MatchEntry = fmMatchEntryComplete '
    • .MatchEntry = fmMatchEntryNone ' no automatic pre-selection at all

    To get a narrowed choice list following the currently typed in partial strings you have to use the comboboxe's _Change() event and move the dictionary declaration to the module head to make it available as well for init as for combo changes.

    The idea is to present an individualized selection following any entry into the combobox [1] and to force a drop down for better overview [2]:

    Option Explicit                               ' declaration head of code module
Dim Dic As Object                             ' make dictionary available at module level

Private Sub CustomerName_Combobox_Change()
    With Me.CustomerName_Combobox
        '[1] narrow data to choose
        .List = Filter(Dic.Keys, .Text, True, vbTextCompare)   ' <~ corrected/2020-03-15
        '[2] expand filter selection (reduced number of valid elements)
        .DropDown
    End With
End Sub

Private Sub UserForm_Initialize()
Dim ws As Worksheet, rCell As Range, srr As Range, Key
Set Dic = CreateObject("Scripting.Dictionary")
Set ws = ThisWorkbook.Worksheets("Backend")
Set srr = ws.Range("b2", ws.Cells(Rows.Count, "b").End(xlUp))

For Each rCell In srr
    If Not Dic.exists(rCell.Value) Then
        Dic.Add (rCell.Value), Nothing
    End If
Next rCell
Me.CustomerName_Combobox.List = Dic.Keys    ' <<  prefix the Me particle to indicate the current instance, not the Userform name itself :-)

End Sub

    Further hint: it's possible to assign all dictionary keys to the combobox .List property at once instead of looping (see _Initialize()). And avoid constructions like IntajForm.CustomerName_Combobox.AddItem Key, just reference the control's name or prefix it by Me to indicate the current instance of the UserForm (class) giving you the public members of that object.

    FYI - For further insight of the Me qualifier and a Userform itself you might profit from reading