ComboBox Filter, search with partial text improvement

Hello there,

I've made a combobox with an filter to search for partial matched values in my database. This is the code i use:

Private Sub ComboBox1_Change()
Call OptimizeCode_Begin
 
 Dim myVal As String, i As Long, ii As Long

    With Me.ComboBox1
        If .Text = "" Then
            .List = myList
        Else
            myVal = .Text
            For i = .ListCount - 1 To 0 Step -1
                If Not UCase$(.List(i, 3)) Like "*" & UCase$(myVal) & "*" Then .RemoveItem (i)
            Next
        End If
    End With

 Call OptimizeCode_End
 
End Sub

There is one problem. When i start typing with the dropdown list expanded, and press a few letters, it does not remove the last item in the list. Now i get a list of matching strings, but there is still one value at the bottum of the list stuck, when its supposed to be deleted. I recon its because you take 1 from the listcount. But without this part, the code does not work.

It looks like this:

I would be really gratefull if someone could help me.

I have this code stored in my useful box.
It seems to work in all circumstances.
Attached file.

Originally Posted by torachan

I have this code stored in my useful box.
It seems to work in all circumstances.
Attached file.

i have troubles implementing it in my program. Thanks though for the example. Is there any way i can adjust my excisting code to work propperly?

Originally Posted by torachan

I have this code stored in my useful box.
It seems to work in all circumstances.
Attached file.

Hey man,

I've just looked into your program one more time. I manged to make it work! Thx alot for the code. Yet there is one problem i mentioned to you before, in the other thread.
When i search for a product where there is two of with the same name. So for example "speakers" one with id 9 and the other with id 10. It will Always select the one with id nr 9 as it finds it first in the list.
In de attachment i've put the new code with the (mostly) working combobox. I hope you could tell me where i am going wrong.

Anyways, still thx for your time.

I can not replicate your code without the two missing 'calls'

the call is just for optimisation. but here is the full workbook. Hope this clarifies.

The filter is working correctly - what do you think is wrong.

If you selected an item with the same name as another item(but with different ID), it would always select the first item of those two. Even if the second one was selected.
This is because i was looking for a partial match in a string by checking each item one by one. As soon as it found a match it would display the match. Even though you might have selected an item further down the list. I fixed this by checking for the list index instead of the name as this is unique.