title: how to create a filtering search box in excel with radio buttons
body: <p>I want to create a search text box that filters data in excel. I have 3 columns of data. Car make, model, and color are the columns. I have radio buttons that when selected I want the search button to search based on that column data. For example, if i have the color radio button selected and search "white" I want the results to return all cars that are white. I need the VBA code for excel. Thanks
  this is the code I have so far. but it only works with one of the radio buttons, when I change to ad different radio button selection it returns nothing.</p>
  
  <p>Here is the screenshot of the excel sheet</p>
  
  <p><a href="https://i.stack.imgur.com/ERHBK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ERHBK.png" alt="enter image description here"></a></p>
  
  <pre><code>Dim myButton As OptionButton
  Dim SearchString As String
  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("A3:F92") '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("txtSearch").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 = "=" &amp; mySearch
    Else
      SearchString = "=*" &amp; mySearch &amp; "*"
    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.WorksheetFunction.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("txtSearch").Object.Text = "" 'ActiveX Control
    'sht.Range("A1").Value = "" 'Cell Input
  
  Exit Sub
  
  'ERROR HANDLERS
  HeadingNotFound:
    MsgBox "The column heading [" &amp; ButtonName &amp; "] was not found in cells " &amp; DataRange.Rows(1).Address &amp; ". " &amp; _
      vbNewLine &amp; "Please check for possible typos.", vbCritical, "Header Name Not Found!"
  
  End Sub
  </code></pre>