Searchbox in Excel

[BJTred]

Greetings I've been searching for a way to create a Search Box to search my Workbook. I found some code on this site, and while, it does work, there are a few things I don't like about it. Here is the code I'm using (slightly edited from it's original):
I'm using Excel 2016.


Sub FindRadio() 'add a command button on your Master Sheet referencing this Macro
res = InputBox("What radio are you looking for? Enter only the last four numbers.")
For w = 2 To Worksheets.Count
With Worksheets(w)
Set Rng = .Cells '<< The Entire Cell 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


My dislikes about it are: It searches each worksheet at a time and makes me click OK to proceed to the next worksheet (of which there are 10 in this workbook). I'd ideally like it to just search the whole workbook and take me to the found information, and maybe have a box that says something like, "Is this what you were looking for?" Clicking yes would end the search, clicking no would take me to the next occurrence, if any. Or if it didn't find the information, it would tell me and let me stop the search. I could live it the way it is, except for the fact that, when it finds the information, I still have to keep clicking OK until I make it through all the worksheets. There is an X at the top of the result box, but instead of stopping the process, the X acts like the OK button and takes me to the next worksheet until I've gone through each one. What I can change in here to get it to do what I'd like? Thanks.

 

[My Aswer Is This]

Will look into this for you

 

[My Aswer Is This]

Try this:

Sub FindRadioTwo()
'Modified 5/31/18 1:05 AM EDT
Dim i As Long
Dim res As String
Dim c As Range
res = InputBox("What radio are you looking for? Enter only the last four numbers.")
    For i = 2 To Sheets.Count
        For Each c In Sheets(i).UsedRange
            If InStr(c.Value, res) > 0 Then
            Application.Goto Sheets(i).Range(c.Address)
            answer = MsgBox("Found " & res & " on " & Worksheets(i).Name & vbNewLine & "Is this what you want?", vbYesNo + vbQuestion, "Found this")
                If answer = vbYes Then Exit Sub
        End If
        Next
    Next
MsgBox "The value  " & res & "  Was not found "
End Sub

 

[BJTred]

Holy Moly! That works like a charm! Exactly what I was looking for. Thank you so much ! !

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Holy Moly! That works like a charm! Exactly what I was looking for. Thank you so much ! !

 

[BJTred]

Can I ask one more thing? The initial box that comes up has a cancel button, but it doesn't cancel, it will take you to Sheet 2 Cell A1, then it will ask if this is what you were looking for, if you click yes, all is good. But if you click no, it will just start going cell by cell across the page. How can I make the cancel but cease the operations? Thanks again. I'm trying to make this as idiot proof as possible, unfortunately I foresee this confusing some.

 

[My Aswer Is This]

Try this:

Sub FindRadioTwo()
'Modified 5/31/18 3:10 PM EDT
Dim i As Long
Dim res As String
Dim c As Range
res = InputBox("What radio are you looking for? Enter only the last four numbers.")
   If res = "" Then MsgBox "You failed to enter a search Value" & vbNewLine & "I will now stop the script": Exit Sub
    For i = 2 To Sheets.Count
        For Each c In Sheets(i).UsedRange
            If InStr(c.Value, res) > 0 Then
            Application.Goto Sheets(i).Range(c.Address)
            answer = MsgBox("Found " & res & " on " & Worksheets(i).Name & vbNewLine & "Is this what you want?", vbYesNo + vbQuestion, "Found  " & res)
                If answer = vbYes Then Exit Sub
        End If
        Next
    Next
MsgBox "The value  " & res & "  Was not found "
End Sub

 

[BJTred]

You're amazing! That's an excellent fix! Thank you so much!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

You're amazing! That's an excellent fix! Thank you so much!