Filter a ComboBox Using Change Event

[tlafferty]

Hi -
I've been fiddling with this for a while. Here's what I'm tying to do:
Remove items from a combobox which do not match what the user types into the combobox. Worded differently, I would like the combo to act as a filter which presents matches based on any part of what the user enters.

To duplicate what I'm presently seeing, enter the following values in cells A1:A4:

A1 Fred Flintstone
A2 Wilma Flintstone
A3 Barney Rubble
A4 Betty Rubble

Set the following properties for the combobox
Name: TempCombo
ColumnCount: 1
ListFillRange: Sheet1!$A$1:$A$4
MatchEntry: 1 - fmMatchEntryComplete

When you enter F, Fr or Fre, the combo will jump to Fred Flintstone, and display Wilma, Barney and Betty as other items in the list.

Similarly, when you enter B, the combo will jump to Barney Rubble and will display Fred, Wilma and Betty as other items in the list.

What I would like to happen:
When you enter F, Fred Flintstone and Wilma Flintstone appear in the list, but Betty and Barney Rubble do not.
When you enter B, Barney and Betty appear as list items, but Fred and Wilma do not.

Similarly, I would like to be able to use any correct sequence of letters for the list item, such as:
ubb or Rub, or Fli, or sto.

Case sensitivity is not important.

So far, I've been playing with the Change event for the control:

Private Sub TempCombo_Change()
    With TempCombo
        For i = .ListCount = -1 To 0 Step -1
            If Not TempCombo.Text Like "*" & .List(i, 1) & "*" Then .RemoveItem (i)
        Next i
        Stop
    End With
End Sub

When run, I get the following error:

Could not get the List property. Invalid argument.

The offending line of code is:

If Not TempCombo.Text Like "*" & .List(i, 1) & "*" Then .RemoveItem (i)

I have tried removing the second parameter like this:

If Not TempCombo.Text Like "*" & .List(i) & "*" Then .RemoveItem (i)

But the error persists.

What I'd like to know is:

• What am I doing wrong?
• Is there a better method?

Thanks for your help!

Thomas