Search Box in Excel

[wdw101]

I would like to add a search box to worksheet 1 in my workbook of 162 sheets. I need the search box to search sheet 23 cell range B11:B342. These cells contain names of employees.

I would like the option of entering a name into the search box and either hitting enter or clicking a "go" button to search. If the search is successful, ideally a function would automatically redirect the user to the located text and if the text cannot be found, a message would appear indicating as such.

When it comes to VBA, I am a very basic user, so I would need the code written out for me and I need to know exactly where to put it and how to get the search box to appear.

I realize I may be asking a question already asked and answered in a different thread so I apologize about that.

Thanks for your help.

 

[VoG]

Try this. Press ALT + F11 to open the Visual Basic Editor, select Module from the Insert menu and paste into the white space on the right

Sub look()
Dim Found As Range
Set Found = Sheets(23).Find(what:=InputBox("Enter name"), LookIn:=xlValues, lookat:=xlWhole)
If Found Is Nothing Then
    MsgBox "Not found", vbInformation
Else
    Application.Goto Found, True
End If
End Sub

Press ALT + Q to close the code window, press ALT + F8 and double click look.

 

[jim may]

Copy the below Code into a Standard Module.

Sub FindEmployee()  'add a command button on your Master Sheet referencing this Macro
Res = InputBox("Who are you looking for?")
Set Rng = Worksheets("Sheet3").Range("B2:B4")   '<<  The Sheet Name and Range to Search
With Rng
    Set MyChoice = .Find(What:=Res)
    If Not MyChoice Is Nothing Then
    Application.Goto MyChoice
    Else: GoTo ExitMyChoice
    End If
End With
Exit Sub
ExitMyChoice:
MsgBox "Could Not Find " & Res
End Sub

 

[wdw101]

Thanks VoG and Jim. Both suggestions worked. I have some more questions:

1. Is it possible to highlight the row when a match is found?

2. Is it possible to imbed a search box with the sheet or a cell so users don't have to click a command button to reach the search box?

Thanks again for your help. I appreciate it.

 

[VoG]

One way: press ALT + F11, in the Project window double click ThisWorkbook and paste in

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Dim Found As Range
If Target.Address(False, False) = "A1" Then
    Cancel = True
    Set Found = Sheets(23).Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole)
    If Found Is Nothing Then
        MsgBox "Not found", vbInformation
    Else
        Application.Goto Found, True
        Found.EntireRow.Interior.ColorIndex = 6
    End If
End If
End Sub

then press ALT + Q to exit the code window.

Now you can enter the search term in cell A1 of any sheet then double click A1 to start the search. Change the A1 in red in the code to use a different cell.

 

[Ingolf]

Hello,

Jim, how can i modify your code to look in all worksheet (without specify a range)?

Thank you.

 

[jim may]

Can a name you enter originally be repeated on any number of the Other Worksheets?
If so, what do you want to see? A Goto the address of the First worksheet and cell it is found in or what?

 

[Ingolf]

Thanks for replay Jim may,

For example look for the name Karl in all sheets. clicking on the button to see the first sheet that is the name, the second push of the button to throw me in the second sheet, etc. ...

And if is not possible how can change your code to search in sheet without defining a range.

Thank you for your time.

 

[jim may]

This might do..

Sub FindEmployee()  'add a command button on your Master Sheet referencing this Macro
res = InputBox("Who are you looking for?")
For w = 2 To Worksheets.Count
With Worksheets(w)
Set Rng = .Cells   '<<  The Entire Sheet is Searched
With Rng
    Set MyChoice = .Find(What:=res)
    If Not MyChoice Is Nothing Then
    Application.Goto MyChoice
    MsgBox "Found " & res & " on " & Worksheets(w).Name
    Else
    MsgBox "Could Not Find " & res & " on " & Worksheets(w).Name
    End If
End With
End With
Next w
Worksheets(1).Activate
End Sub

 

[Ingolf]

Jim may,

Marvelous, is exactly what I needed. Thanks for your time and effort given.
Thank you very much.

Ingolf