Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Text Search/Filter Button

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Text Search/Filter Button

    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: www.TheSpreadsheetGuru.com
    
    
    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



    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"

  2. #2
    New Member
    Join Date
    Feb 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text Search/Filter Button

    The OneDrive link to an example DB is here
    HTML Code:
    https://1drv.ms/x/s!Aqu9UKhK8oUTgYdWdZk7-G2UCklXMw

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,344
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Text Search/Filter Button

    Cross posted https://www.excelforum.com/excel-pro...er-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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  4. #4
    New Member
    Join Date
    Feb 2018
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Text Search/Filter Button

    Sorry. How do I delete this?

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,344
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Text Search/Filter Button

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •