Searchbox in Excel

BJTred

New Member
Joined
May 30, 2018
Messages
8
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Try this:
Code:
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
 
Upvote 0
Holy Moly! That works like a charm! Exactly what I was looking for. Thank you so much ! ! :)
 
Upvote 0
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.
 
Upvote 0
Try this:
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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
Back
Top