Autocomplete ActiveX combobox

[tiredofit]

I am trying to create an autocompleting ActiveX combobox and have managed to get it to work almost perfectly.

Private Sub ComboBox1_Change()
    Call CreateCustomList
    Me.ComboBox1.DropDown
End Sub

Sub CreateCustomList
    Dim Val As String
    Val = Userform.ComboBox1.Value
    Dim Coll As Collection
    Set Coll = New Collection
    Dim i As Integer
    For i = 2 To 10
        If InStr(1, Sheet1.Cells(i, 1).Value, Val, vbTextCompare) <> 0 Then
            Coll.Add Sheet1.Cells(i, 1).Value
        End If
    Next i
    If Coll.Count <> 0 Then
        Dim DataArray()
        ReDim DataArray(1 To Coll.Count, 1 To 1)
        For i = 1 To Coll.Count
            DataArray(i, 1) = Coll(i)
        Next i
        Sheet2.Cells.ClearContents
        Sheet2.Cells(1, 1).Resize(Coll.Count, 1).Value = DataArray()
        Userform.ComboBox1.List = DataArray()
        Erase DataArray()
        Userform.ComboBox1.ListRows = 8
    Else
        Sheet2.Cells.ClearContents
        Userform.ComboBox1.Clear
        Userform.ComboBox1.ListRows = 1
    End If

    Set Coll = Nothing
    
End Sub

Sheet1 contains these values:

Apple
Apple1
Apple2
Orange
Orange2
Orange3
Pear
Pear2
Pear3

If I fire up the userform and type A into its combobox, the drop down list shows all values that contain the letter A, regardless of the case, which is fine, (so in this case, all 9 values will be shown).

If I type in the next letter p, I only see the Apples and Pears because they're the only ones that contain both the letters A and p, also fine.

Now if I type in the next letter z, nothing shows up, again as expected.

The problem arises if I now delete the letter z.

I expect to see Apples and Pears in the drop down.

Instead, I only see Apple. I have to delete the letter p in order for the drop down to show Apple, Apple1, Apple2, Pear, Pear2, Pear3.

I tried adding these two lines:

        Userform.ComboBox1.ListRows = 8
        Userform.ComboBox1.ListRows = 1

but it didn't help.

Is this a bug or have I missed something?

Thanks

 

[bobsan42]

try to replace this:

Val = Userform.ComboBox1.Value

with this:

Val = Userform.ComboBox1.Text

what is the value of VAL when the list remains blank?

 

[tiredofit]

try to replace this:

Val = Userform.ComboBox1.Value

with this:

Val = Userform.ComboBox1.Text

what is the value of VAL when the list remains blank?

VAL = "" when the list is blank.

I tried your suggestion by changing Value to Text but it hasn't solved the problem.

Instead, I added a message box and it did the trick because the focus is off the userform.

Sheet2.Cells.ClearContents
        Userform.ComboBox1.Clear
        Userform.ComboBox1.ListRows = 1

Msgbox "No such value"

after clicking OK to close the message box, the user needs to click into the combobox within the userform and when the last letter is deleted, Apples and Pears are shown in the drop down.

 

[bobsan42]

There is something strange in your explanation. Actually I tested your code and despite it being strange it works as it needs to.
What puzzles me the most is your explanation about showing Apples and Pears when you type AP? This code cannot produce such result.

The other thing i find odd is thee mixed use of ranges, collections and arrays, and the way you transfer data beteen them.
It is also inefficient to build the full data list on each key stroke.

 

[tiredofit]

There is something strange in your explanation. Actually I tested your code and despite it being strange it works as it needs to.
What puzzles me the most is your explanation about showing Apples and Pears when you type AP? This code cannot produce such result.

The other thing i find odd is thee mixed use of ranges, collections and arrays, and the way you transfer data beteen them.
It is also inefficient to build the full data list on each key stroke.

Thanks for testing.

I've looked at solutions built by others and none worked exactly as I want, so decided to build it myself.

I'll post an update later.
















]#]=-

 

[tiredofit]

Thanks for testing.

I've looked at solutions built by others and none worked exactly as I want, so decided to build it myself.

I'll post an update later.
















]#]=-

Apologies, it was my mistake. I do agree with you, in that typing ap DOES NOT show Apples and Pears, it only shows Apples.

However, my point is correct in that if you typed Apz, nothing shows up but if you delete the z, only Apple2 is shown in the drop down. If you subsequently delete the p, then full list is shown.

I found you CAN get what I want if after typing apz, you press the Enter key. The drop down sows nothing but if you now delete the z, you will see Apple2 and Apple3.

The MatchEntry property on the combobox is 2 - fmMatchEntryNone.

The Style property is 0 - fmStyleDropDownCombo.

 

[DanteAmor]

On other occasions, after so much searching, I did this and it works for me.
Add these lines:

        TextBox1.SetFocus
        ComboBox1.SetFocus

I just hope you have a textbox or another control, it can be another combo or listbox.

Sub CreateCustomList()
    Dim Val As String
    Val = UserForm.ComboBox1.Value
    Dim Coll As Collection
    Set Coll = New Collection
    Dim i As Integer
    For i = 2 To 10
        If InStr(1, Sheet1.Cells(i, 1).Value, Val, vbTextCompare) <> 0 Then
            Coll.Add Sheet1.Cells(i, 1).Value
        End If
    Next i
    If Coll.Count <> 0 Then
        Dim DataArray()
        ReDim DataArray(1 To Coll.Count, 1 To 1)
        For i = 1 To Coll.Count
            DataArray(i, 1) = Coll(i)
        Next i
        Sheet2.Cells.ClearContents
        Sheet2.Cells(1, 1).Resize(Coll.Count, 1).Value = DataArray()
        UserForm.ComboBox1.List = DataArray()
        Erase DataArray()
        UserForm.ComboBox1.ListRows = 8
        TextBox1.SetFocus
        ComboBox1.SetFocus
    Else
        Sheet2.Cells.ClearContents
        UserForm.ComboBox1.Clear
        UserForm.ComboBox1.ListRows = 1
    End If

    Set Coll = Nothing
   
End Sub

 

[tiredofit]

On other occasions, after so much searching, I did this and it works for me.
Add these lines:

        TextBox1.SetFocus
        ComboBox1.SetFocus

I just hope you have a textbox or another control, it can be another combo or listbox.

Sub CreateCustomList()
    Dim Val As String
    Val = UserForm.ComboBox1.Value
    Dim Coll As Collection
    Set Coll = New Collection
    Dim i As Integer
    For i = 2 To 10
        If InStr(1, Sheet1.Cells(i, 1).Value, Val, vbTextCompare) <> 0 Then
            Coll.Add Sheet1.Cells(i, 1).Value
        End If
    Next i
    If Coll.Count <> 0 Then
        Dim DataArray()
        ReDim DataArray(1 To Coll.Count, 1 To 1)
        For i = 1 To Coll.Count
            DataArray(i, 1) = Coll(i)
        Next i
        Sheet2.Cells.ClearContents
        Sheet2.Cells(1, 1).Resize(Coll.Count, 1).Value = DataArray()
        UserForm.ComboBox1.List = DataArray()
        Erase DataArray()
        UserForm.ComboBox1.ListRows = 8
        TextBox1.SetFocus
        ComboBox1.SetFocus
    Else
        Sheet2.Cells.ClearContents
        UserForm.ComboBox1.Clear
        UserForm.ComboBox1.ListRows = 1
    End If

    Set Coll = Nothing
  
End Sub

Thanks, indeed your suggestion does work but I need to add a textbox to my userform!

 

[DanteAmor]

Thanks, indeed your suggestion does work but I need to add a textbox to my userform!

Do you only have one combo in your userform?
Don't have another combo or a listbox?

 

[tiredofit]

Do you only have one combo in your userform?
Don't have another combo or a listbox?

It's OK, my userform has an OK and Cancel button too, so I just set the focus to either one.

Is it a bug that you can to set the focus to another control, then set it back to the combobox?