Code

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

Many lists, such as a phone directory list will allow a
quick selection of a list item by entering one or more of
the first several characters in the item name. i.e., I
enter "sm" in an input box and the cell of Smith is
selected. And if I enter just "s" the first "S" entry is
selected.

what does the code to do this look like?

Thank you,

Jon
 
Assume the list is sorted and in column A starting in row 2 of sheet1, code
is for a textbox in a userform.

Private Sub Textbox1_change()
Dim rng as Range
Dim res as Variant
With Worksheets("Sheet1")
set rng = .Range(.Cells(2,1),.Cells(2,1).End(xldown)
res = Application.Match(Textbox1.Text,rng,1)
if not iserror(res) then
Application.Goto reference:=rng(res), Scroll:=True
else
application.Goto reference = .Range("A1"), Scroll:=True
end if
End With
End Sub

If you use a combobox and the list of items is in the list of the combobox
(again, sorted), then this capability is builtin.
 
Back
Top