How to create a search box located on its own sheet to search thru an entire Excel workbook?

[Kclynn]

This is the current code. It is not working for me.

Sub SearchBox()
'PURPOSE: Filter Data on User-Determined Column & Text/Numerical value
'SOURCE: www.TheSpreadsheetGuru.com

Dim myButton As OptionButton
Dim SearchString As String
Dim ButtonName As String
Dim wb As Workbook
Dim myField As Long
Dim DataRange As Range
Dim mySearch As Variant

'Load Sheet into A Variable
Set wb = ActiveWorkbook

'Unfilter Data (if necessary)
On Error Resume Next
sht.ShowAllData
On Error GoTo 0

'Filtered Data Range (include column heading cells)
Set DataRange = sht.Range("A1:C31") 'Cell Range
'Set DataRange = sht.ListObjects("Table1").Range 'Table

'Retrieve User's Search Input
mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form
'mySearch = sht.OLEObjects("UserSearch").Object.Text 'ActiveX Control
'mySearch = sht.Range("A1").Value 'Cell Input

'Determine if user is searching for number or text
If IsNumeric(mySearch) = True Then
SearchString = "=" & mySearch
Else
SearchString = "=*" & mySearch & "*"
End If

'Loop Through Option Buttons
For Each myButton In sht.OptionButtons
If myButton.Value = 1 Then
ButtonName = myButton.Text
Exit For
End If
Next myButton

'Determine Filter Field
On Error GoTo HeadingNotFound
myField = Application.WorkBookFunction.Match(ButtonName, DataRange.Rows(1), 0)
On Error GoTo 0

'Filter Data
DataRange.AutoFilter _
Field:=myField, _
Criteria1:=SearchString, _
Operator:=xlAnd

'Clear Search Field
sht.Shapes("UserSearch").TextFrame.Characters.Text = "" 'Control Form
'sht.OLEObjects("UserSearch").Object.Text = "" 'ActiveX Control
'sht.Range("A1").Value = "" 'Cell Input

Exit Sub

'ERROR HANDLERS
HeadingNotFound:
MsgBox "The column heading [" & ButtonName & "] was not found in cells " & DataRange.Rows(1).Address & ". " & _
vbNewLine & "Please check for possible typos.", vbCritical, "Header Name Not Found!"

End Sub

Thank you.

 

[Logit]

.
The following requires only one CommandButton. You will need to add your own code (notation in middle of macro) to perform
the action/s you are seeking AFTER locating the search term :

Sub srchShts()
    Dim ws As Worksheet, sh As Worksheet
    Dim Rng As Range, r As Range
    Dim myvalue As Variant
    Dim SrchStrg As String
    Dim newRow As Long
    
    'Set ws = Sheets("Period 1")
    Set ws = ActiveSheet
    myvalue = InputBox("Enter Term To Search")
    SrchStrg = myvalue2
    
    For Each sh In Sheets

                With sh.Range("A1:C31")       'edit range as required
                
                Set r = .Find(what:=SrchStrg, LookIn:=xlValues, After:=.Range("A1"))  'find the cell whose value is equal to SrchStrg and activate it
                    If Not r Is Nothing Then
                    'If the term is found, do something here (add your code)
                    
                End With
        
    Next sh
End Sub

 

[Logit]

In the above macro, edit this line :

SrchStrg = myvalue2

To this line :

SrchStrg = myvalue

My apologies ...