ComboBox with autocomplete dropdown OR add to list

[jimmisavage]

Hi,
I have a list in column A and ComboBox1 is in Userform1

When a user begins to type in the combobox I would like a list of 3 options to dropdown filtered on what they're typing. If the user continues to type and it's not in the list I would like what the user is typing to be added to the list.

So, if i have a list of names in column A and the user types 'STE' the dropdown list would show Stephen, Steven and Steve. I would also like it if the user typed 'EVE' it would show Eve, StEVEn, StEVE etc so the search would search the whole word, not just the beginning.

I have tried a few different options in the combobox but I can't get it to work as above. Can anyone help?

 

[Norie]

What have you tried?

 

[jimmisavage]

What have you tried?

The closest I could find was this but I didn't have much success with it

[COLOR=#252C2F][FONT=Courier]Private Sub ComboBox1_Change()[/FONT][/COLOR]    Dim e, temp
    With Me
        temp = .ComboBox1.Value
        If Not .ComboBox1.MatchFound Then
            .ComboBox1.Clear
            If Len(temp) Then
                For Each e In Sheets("sheet1").Cells(1).CurrentRegion.Value
                    If (e <> "") * (e Like "*" & temp & "*") Then
                        .ComboBox1.AddItem e
                    End If
                Next
                If .ComboBox1.ListCount > 0 Then
                    .ComboBox1.ListIndex = 0
                End If
            End If
        End If
    End With [COLOR=#252C2F][FONT=Courier]End Sub[/FONT][/COLOR]

 

[Norie]

You probably want to look at using Filter.

Dim arrIn As Variant ' inital list
Private Sub ComboBox1_Change()
Dim arrOut As Variant
    
    arrOut = Filter(arrIn, ComboBox1.Value, True)
    
    If UBound(arrOut) <> -1 Then
        ComboBox1.List = arrOut
    Else
        ComboBox1.List = arrIn
    End If
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.List = Range("A1").CurrentRegion.Value
    arrIn = Application.Transpose(ComboBox1.List)
End Sub

 

[jimmisavage]

I found this thread here which seems to give a pretty close solution to what I need but it throws up an error when i start typing. The error highlights Me.ComboBox1.Clear but I can't understand why. That's the name of my combobox!

It also doesn't have the part where it would add to my list if it's not already there.

 

[jimmisavage]

You probably want to look at using Filter.

Dim arrIn As Variant ' inital list
Private Sub ComboBox1_Change()
Dim arrOut As Variant
    
    arrOut = Filter(arrIn, ComboBox1.Value, True)
    
    If UBound(arrOut) <> -1 Then
        ComboBox1.List = arrOut
    Else
        ComboBox1.List = arrIn
    End If
End Sub

Private Sub UserForm_Initialize()
    ComboBox1.List = Range("A1").CurrentRegion.Value
    arrIn = Application.Transpose(ComboBox1.List)
End Sub

This is messing up a few things for me. I added the code inside my bombobox. However, when i click on my button to launch the userform it hightlights UserForm1.Show as an error on my button.

I have no idea what's going on there!

 

[Norie]

What I posted is an example that I tested using a simple userform with a combobox populated from column A, it's not really mean to be just plugged into exisiting code.

What code do you have in your userform and what errors are you getting?

 

[jimmisavage]

What I posted is an example that I tested using a simple userform with a combobox populated from column A, it's not really mean to be just plugged into exisiting code.

What code do you have in your userform and what errors are you getting?

That's exactly what I've done as well. Only I added a button to load the userform to test. I currently have no code in my userform and added your code to my combobox

 

[Norie]

What happens if you run the userform manually from the VBE rather than the button?

 

[jimmisavage]

What happens if you run the userform manually from the VBE rather than the button?

I get a runtime error