Search box for whole workbook

DomiDarko

New Member
Joined
Jun 20, 2013
Messages
42
Greetings to the Excel community.

I am trying to develop my workbook design for my employers. I am advanced with formulas but have just started VBA last week and have a book arriving tmrw to help me.
So far i have already added a search function using a command button and text box to each worksheet and a toggle button to find and hide/show rows users dont need automatically with great success :)
I am currently adapting code from the internet and only writing small bits here and there as my knowledge expands.

Here is the code i have adapted so far. Its not working correctly and i cant seem to figure it out. The command button and textbox will sit on the first worksheet and needs to search for a name (example: John) in the workbook and either display which sheet the name is found on or jump in turn to each name until you reach the correct sheet.
Do i need to set a range for the variable (ws)?
Many Thanks for any input.

Private Sub CommandButton1_Click()

Dim search As String
Dim ws As Worksheet
Dim Answer As String
search = TextBox1.Text
On Error GoTo ErrorMessage

Application.EnableEvents = True

For Each ws In ActiveWorkbook.Worksheets
Cells.Find(What:=search, After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Select

If ActiveCell.Row Then
Answer = MsgBox("Found at " & ActiveSheet.Name & " Continue searching ? ", vbYesNo)
If Answer = vbNo Then Exit Sub

ErrorMessage:
MsgBox ("Not Found")

End If
Next ws
End Sub
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

storm8

Active Member
Joined
Apr 7, 2010
Messages
327
I think that after:=activecell might be the problem, and I think you do not need it at all
furthermore you are not specifiying correctly where the search occurs
last, I think that select will not work if result is on different ws

maybe this will work

Code:
[COLOR=#333333]dim r as range
set r = ws.Cells.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
if not r is nothing then application.goto(r)
[/COLOR]
 

DomiDarko

New Member
Joined
Jun 20, 2013
Messages
42
I think that after:=activecell might be the problem, and I think you do not need it at all
furthermore you are not specifiying correctly where the search occurs
last, I think that select will not work if result is on different ws

maybe this will work

Code:
[COLOR=#333333]dim r as range
set r = ws.Cells.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
if not r is nothing then application.goto(r)
[/COLOR]

Thanks Storm8 - that makes a lot of sense. However, it is still not working. Nearly there i feel, just missing something :confused:
 

storm8

Active Member
Joined
Apr 7, 2010
Messages
327
my mistake
working solution:
Code:
Sub find()


Dim ws As Worksheet
Dim search As String
search = "karel"


For Each ws In ThisWorkbook.Worksheets
    Dim r As Range
    Set r = ws.Cells.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not r Is Nothing Then Application.Goto (Sheets(ws.Name).Range(r.Address))


Next ws


End Sub
 

DomiDarko

New Member
Joined
Jun 20, 2013
Messages
42

ADVERTISEMENT

my mistake
working solution:
Code:
Sub find()


Dim ws As Worksheet
Dim search As String
search = "karel"


For Each ws In ThisWorkbook.Worksheets
    Dim r As Range
    Set r = ws.Cells.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    If Not r Is Nothing Then Application.Goto (Sheets(ws.Name).Range(r.Address))


Next ws


End Sub

Thanks Storm8 - This is working, great stuff!
If possible i would like for a msg box to pop up after it searchs each sheet to say 'not found on the 'activesheet.name - continue?', so users know it has searched a particular sheet but im getting a syntax error when i try to add an 'If answer = vbNo Then MsgBox" etc, etc.
 

DomiDarko

New Member
Joined
Jun 20, 2013
Messages
42
Whoops, thats not right!
I will try and adapt the 'not found' to say what sheet it isnt found on!
 

DomiDarko

New Member
Joined
Jun 20, 2013
Messages
42

ADVERTISEMENT

No luck so far. I'm afraid my novice skills are trying to add the wrong code for this.
Am i on the wrong path?

ErrorMessage:
MsgBox ("Not Found on " & ActiveSheet.Name ",)
 

DomiDarko

New Member
Joined
Jun 20, 2013
Messages
42
I've done it. Many Thanks.

ErrorMessage:
MsgBox ("Not Found on " & ActiveSheet.Name)
 

DomiDarko

New Member
Joined
Jun 20, 2013
Messages
42
I've done it. Many Thanks.

ErrorMessage:
MsgBox ("Not Found on " & ActiveSheet.Name)

Ah, i've just discovered that wont work - only picks up the active sheet not the next searched sheet.

Couple of minor problems
1. It always searchs the 1st sheet and the actual text box!!! So it always say found on sheet 1. I need to skip the 1st worksheet.
2. It seems to sometimes search the same sheet multiple times?
3. When it searchs the sheets in order and cant find anything i need it to say not found on sheet# etc.

Any help would be great - this is very nearly complete!
 

storm8

Active Member
Joined
Apr 7, 2010
Messages
327
Code:
Sub find()


    Dim ws As Worksheet
    Dim search As String
    search = "karel"
    
    For Each ws In ThisWorkbook.Worksheets
        Dim r As Range
        If ws.Name = ThisWorkbook.Sheets(1).Name Then GoTo nws
        Set r = ws.Cells.find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        
        If Not r Is Nothing Then
            Application.Goto (Sheets(ws.Name).Range(r.Address))
            MsgBox "found on " & ws.Name
        Else
            MsgBox "not found on " & ws.Name
        End If
nws:
    Next ws
End Sub

for Q2: search will serach all occurences on worksheet. if you do not want this, place "goto nws" after the "found on..." msgbox
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

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