searchbox in excel sheet

  • Thread starter Thread starter Swingleft
  • Start date Start date
S

Swingleft

Hi,

I have a column of about 1000 names surnames.

to enter a new name, or searching for a name
I would like to have some kind of search box so when i type the first letter
or the first 2 letters, i only see the names which starts with those
letters.

Has anyone any idea / solution?

thanks for all the help

swingleft
 
Hi,

I have a column of about 1000 names surnames.

to enter a new name, or searching for a name
I would like to have some kind of search box so when i type the first letter
or the first 2 letters, i only see the names which starts with those
letters.

Has anyone any idea / solution?

thanks for all the help

swingleft
Click to expand...

try this... this may server your purpose

1. convert the data into a table say "Table1"

2. Write a macro that filters the table based on a value passed:
Sub Macro1(Val As String)
Range("Table1[[#Headers],[Column1]]").Select
Selection.AutoFilter
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1,
Criteria1:=LCase$(Val) & "*", Operator:=xlAnd
End Sub

3. use cell A1 in your sheet to enter the search criteria.

4. Call the macro in the worksheet_change event
Private Sub Worksheet_Change(ByVal Target As Range)

Macro1 LCase$(Target.Cells(1, 1))
End Sub
 
Back
Top