Text Search/Filter Button

nadiavkhan

New Member
Joined
Feb 13, 2018
Messages
3
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:
<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"
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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