Drop-Down List Search Capability

  • Thread starter Thread starter aehaver
  • Start date Start date
A

aehaver

I have a long data validation list of over 500 items. Is there a way to
program it so that if you type in the first couple of letters of an item in
the list it jumps to that area of the list?
 
you can do it with a userform .... and with 500 items you'd be surprised at
how fast it is!

demo

in this demo, add a Userform with two buttons, a textbox and a listbox ...
leave the default names
in a sheet, put 500 names in column D
the item selected using the listbox will be placed in cell A1 when the
sencond button is clicked

here is the userform code:

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
If ListBox1.ListIndex = -1 Then Exit Sub
Range("A1") = ListBox1.Value
Unload Me
End Sub
Private Sub TextBox1_Change()
loadData
End Sub
Private Sub UserForm_Initialize()
loadData
End Sub
Sub loadData()
Dim index As Long
Dim text As String
ListBox1.Clear
For index = 1 To 500
text = Cells(index, "D")
If text Like TextBox1.text & "*" Then
ListBox1.AddItem text
End If
Next
End Sub



download from here:
http://www.xl-expert.com/listbox_with_filter.htm
 
Back
Top