Search Box in Excel

wdw101

New Member
Joined
Feb 12, 2012
Messages
5
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.
 
Last edited:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Code:
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,480
Copy the below Code into a Standard Module.


Code:
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

New Member
Joined
Feb 12, 2012
Messages
5
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

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

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

Rich (BB code):
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

Banned
Joined
Mar 20, 2011
Messages
809
Hello,

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

Thank you.
 

jim may

Well-known Member
Joined
Jul 4, 2004
Messages
7,480

ADVERTISEMENT

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

Banned
Joined
Mar 20, 2011
Messages
809
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

Well-known Member
Joined
Jul 4, 2004
Messages
7,480
This might do..

Code:
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

Banned
Joined
Mar 20, 2011
Messages
809
Jim may,

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

Ingolf
 

Watch MrExcel Video

Forum statistics

Threads
1,122,922
Messages
5,598,901
Members
414,267
Latest member
chemhany

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top