Solved: auto complete based on the first and last name (combobox / UserForm)
I have a userform with a combobox, and also have a spreadsheet "SheetNames" column "A" is a column with various names.
I would like to create a complete high.
example:
I have the following name (spreadsheet "SheetNames" column "A")
ABNER ANTONIO ANDRE DE Moura
I'd like a way I can enter any part of the name.
If I enter ANTONIO, then the code will search all ANTONIO, if you have more than 1 I will bring all if not auto complete ABNER ANTONIO ANDRE DE Moura.
If I enter Moura, then the code will search all Moura, if you have more than 1 I will bring all if not auto complete ABNER ANTONIO ANDRE DE Moura.
I don't see how that would work with a combobox. Try this using a TextBox and a ListBox
[VBA]Private Sub TextBox1_Change()
Dim x As String, r As Range
x = TextBox1
If Len(x) < 4 Then Exit Sub
ListBox1.Clear
With Sheets(1).Columns(1)
filtercriteria = "*" & x & "*"
.AutoFilter Field:=1, Criteria1:=filtercriteria
Set r = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).SpecialCells(xlCellTypeVisible)
.AutoFilter
End With
For Each CEL In r
ListBox1.AddItem CEL
Next
End Sub[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.