VBA combobox with autocomplete and search as you type

Question

I have the below code which creates a userform with autocomplete which I copied from another website. I would like to modify this to include a "search as you type" feature. for example:

the table the combo box references is 1 column and includes items like this:

chevy truck
ford truck
truck
chevy car
ford car
car

Currently when the user types "truck" the only result and suggestion is "truck" and will not show 'chevy truck'

I would want the drop down list to show something like this when the user searches 'truck'

truck
chevy truck
ford truck

or if the user types "tr" the list will show:

truck
chevy truck
ford truck

user types 'che'...list shows:

chevy truck
chevy car

etc.

Private Sub OEM_Change()
Dim x, dict
Dim i As Long
Dim str As String
'x = this is where i need help.
Set dict = CreateObject("scripting.dictionary")
str = Me.OEM.Value
    If str <> "" Then
        For i = 1 To UBound(x, 1)
            If InStr(LCase(x(i, 1)), LCase(str)) > 0 Then
                dict.Item(x(i, 1)) = ""
            End If
        Next i
    Me.OEM.List = dict.keys
    Else
    Me.OEM.List = x
    End If
    Me.OEM.DropDown

End Sub

Private Sub UserForm_Initialize()

With Vertical
     .AddItem "vertical1"
     .AddItem "vertical2"
     .AddItem "vertical3"
     .AddItem "vertical4"
     .AddItem "vertical5"
End With

End Sub

Private Sub Vertical_Change()

Dim index As Integer
 index = Vertical.ListIndex

Select Case index
     Case Is = 0
         With OEM
             .RowSource = "Namedrange1"
         End With
     Case Is = 1
         With OEM
             .RowSource = "Namedrange2"
         End With
     Case Is = 2
         With OEM
            .RowSource = "Namedrange3"
         End With
    Case Is = 3
         With OEM
            .RowSource = "Namedrange4"
         End With
    Case Is = 4
         With OEM
            .RowSource = "Namedrange5"
         End With

End Select

End Sub

for reference: some of this code came from this thread

searchable combo box with a list of sugggestion on a userform

Solution

ok guys I figured it out. I dim'ed another variable as "p". p = vertical.value, then x = worksheets("sheet2").range(p).value. works perfect. thanks to all for your help

Comments

[dusty_j]

I believe you have posted almost the exact same code I did. you may have found the website I pulled mine from. doesn't really help me though and this code only filters the list to an exact match of what you typed. I need a modified version that can search a string and return any part of that string

[ASH]

I edited my original answer. Now, I can't seem to post the VBA code into the window...very weird... Anyway, check the link and you 'll get all code from there. People at this site don't take favorably to just posting a link, so I listed all steps along with the link. Good luck.

[sta]

A link to a solution is welcome, but please ensure your answer is useful without it: add context around the link so your fellow users will have some idea what it is and why it’s there, then quote the most relevant part of the page you're linking to in case the target page is unavailable. Answers that are little more than a link may be deleted.