Text Search/Filter Button

[nadiavkhan]

I am trying to setup a macro where I can type a word into a textbox, click a button, and filter a table based on the value of the text box. The code I am using was made available on the SreadSheetGuru.

<code>Option Explicit
Sub SearchBox()
'PURPOSE: Filter Data on User-Determined Column & Text
'SOURCE: [URL="http://www.TheSpreadsheetGuru.com"]www.TheSpreadsheetGuru.com[/URL]


Dim myButton As OptionButton
Dim MyVal As Long
Dim ButtonName As String
Dim sht As Worksheet
Dim myField As Long
Dim DataRange As Range
Dim mySearch As Variant
   
'Load Sheet into A Variable
  Set sht = ActiveSheet


'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("a4:f7") '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


'Loop Through Option Buttons
  For Each myButton In ActiveSheet.OptionButtons
      If myButton.Value = 1 Then
        ButtonName = myButton.Text
        Exit For
      End If
  Next myButton
  
'Determine Filter Field
  On Error GoTo HeadingNotFound
    myField = Application.WorksheetFunction.Match(ButtonName, DataRange.Rows(1), 0)
  On Error GoTo 0
  
'Filter Data
  DataRange.AutoFilter _
    Field:=myField, _
    Criteria1:="=" & mySearch & "*", _
    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

</code>
The problem I have is that I receive an error: "runtime error 80070057 -
the item with the specified name wasn't found." And the VBA code
highlights "mySearch = sht.Shapes("UserSearch").TextFrame.Characters.Text 'Control Form"

 

[nadiavkhan]

The OneDrive link to an example DB is here

https://1drv.ms/x/s!Aqu9UKhK8oUTgYdWdZk7-G2UCklXMw

 

[Fluff]

Cross posted https://www.excelforum.com/excel-programming-vba-macros/1219786-text-search-filter-button.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[nadiavkhan]

Sorry. How do I delete this?

 

[Fluff]

You do not need to delete it.
All we ask is, if you post a question to 2 or more sites, please post links to all the sites.