Search box for whole workbook

[DomiDarko]

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

 

[storm8]

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

[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]

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

[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

 

[storm8]

my mistake
working solution:

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]

my mistake
working solution:

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]

Whoops, thats not right!
I will try and adapt the 'not found' to say what sheet it isnt found on!

 

[DomiDarko]

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]

I've done it. Many Thanks.

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

 

[DomiDarko]

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]

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