For data valaidation with Auto-complete, the easiest thing to do is to add a userform with a textbox and a listbox to your project, then use event code like this in the worksheet (Chage "RangeWithList" to the cells addresses where you want autocomplete with validataion.) Change the size of the userform and listbox to show enough of the list of allowed values, and make it pretty
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("RangeWithList")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
UserForm1.Show
End Sub
And in the userform's codemodule, use this code: When you select the cell, your will see the userform, and can begin typing in the TextBox to reduce the list entries in the listbox - if you get to a few, you can click the entry in the listbox; if you get to just one, it is entered in the cell without having to click the listbox at all.
Private Sub ListBox1_Change()
Application.EnableEvents = False
ActiveCell.Value = Me.ListBox1.List(Me.ListBox1.ListIndex)
Application.EnableEvents = True
Unload Me
End Sub
Private Sub TextBox1_Change()
Me.ListBox1.List = Range("Emp_list").Value
For i = Me.ListBox1.ListCount To 1 Step -1
If Not UCase(Me.ListBox1.List(i - 1)) Like UCase(Me.TextBox1.Value & "*") Then
Me.ListBox1.RemoveItem i - 1
End If
Next i
If Me.ListBox1.ListCount = 1 Then
Application.EnableEvents = False
ActiveCell.Value = Me.ListBox1.List(0)
Application.EnableEvents = True
Unload Me
End If
End Sub
Private Sub UserForm_Initialize()
Me.ListBox1.List = Range("Emp_list").Value
End Sub
Bookmarks