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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows
Will look into this for you
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows
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
 

BJTred

New Member
Joined
May 30, 2018
Messages
8
Holy Moly! That works like a charm! Exactly what I was looking for. Thank you so much ! ! :)
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

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

Well-known Member
Joined
Jul 5, 2014
Messages
17,264
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,366
Messages
5,601,204
Members
414,434
Latest member
Riyen

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