How To Create A Filtering Search Box For Your Excel Data

Why A Search Box?

Ever since search engines came into fruition (I can still remember those Yahoo! commercials), search boxes have become an experience that everyone has become accustomed to.  Can you think of the last time you have been on your computer and haven't interacted with some sort of search box or even an omnibox?  

It's unfortunate that Excel doesn't have a form control search box (maybe in the future?) as I could see that type of tool opening the doors to a ton of creative and time saving functionalities.  But luckily you can create a search box on your own.  In this post I will walk you through how to create a gorgeous-looking search box that can filter your data to only show your search results.  First I will show you how to set it up and then you will learn how to tweak the VBA code to fit your setup.

Creating Your Search Box User Interface

Your search UI (user-interface) can look however you want as long as there is a place for your user(s) to enter in some text and a search button for them to click.  The above image shows how I will typically create my UI.  I use a nice clean textbox to hold the search text and flat-styled, rounded rectangle shape for the button.

Get Fancy With Option Buttons

Instead of only allowing your users to filter on a single column, why not let them search through a few? By integrating Option Buttons with your search box you can have your users specify which column they want to search in.  To insert the Option Buttons you will need to

  1. Navigate to your Developer Tab in the Ribbon
  2. Click the Insert drop down button in the Controls group
  3. Select the Option Button Form Control (first row, last icon)
  4. Your mouse should now look like cross hairs and you will just want to click somewhere on your spreadsheet to draw the Option Button

After you draw a couple of Option Buttons, you can drag them into place so that they are relatively close to your search box.  You can use the alignment tools to make everything look professional with even spacing.

One Pitfall

The one pitfall that I could not seem to get around is that fact that after entering in your search text, you need to click outside of the textbox before you can click on the Search button.  There are two workarounds that I could think of:

  1. Instead of using a form control textbox, you can use either a Cell or ActiveX Textbox to hold the search text (I have lines of code in the below VBA macros commented out that can handle these situations)
  2. Assign a keyboard shortcut to execute the macro, alleviating the need to click the Search button

I typically go the shortcut route as I like having the ability to place my search box wherever I want on my spreadsheet. Also, ActiveX controls can sometimes be glitchy depending on which version of Office you are using, so beware if you end up using this route.

Now For The VBA!

Naming Your Objects

The key to getting this code to work well is to setup your objects (aka form controls) properly.  First you will need to determine the name of the text box that is holding your search term.  To do this, you need to select the text box and then look at the Name Box (which is located to the left of the Formula Bar).  

Typically you will see a default name of "Text Box 1", however you can change this name to something more meaningful like "UserSearch".  Make sure you hit the Enter key immediately after typing in your new name to apply the name change!  If you click outside of the Name Box before hitting enter, your text box will revert back to it's previous name.

For your Option Buttons you will not need to change their object names (unless you really want to). You will, however, need to ensure that their text is verbatim with the data headings you will be filtering on. Notice how all my example Option Button's have the exact same text as the headings in my data.  This is EXTREMELY important as the VBA code below will be filtering based on the text associated with the selected Option Button.

Searching For Text Only

This macro will allow you to filter on any column with a text value within it.  The macro uses an open ended search (designated by the asterisk symbol before and after the search term).  This means that you could search "whi" and the search would show any cell containing those 3 characters.  If you want your search box to only filter on exactly what the user types, just remove the asterisks from the VBA code.

To set up this code with your data you will need to designate your data range for the variable DataRange and you will also need to modify your text box name inside the Shapes reference.  If your data does not start in Column A you may need to add or subtract from the myField variable to ensure you are filtering on the correct column number associated with your data set.

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:E31") '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

Searching For Text & Numerical Values

This VBA code is the exact same setup as the previous code but it has a few extra lines added to handle both numerical and text inputs from your user.

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 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:E31") '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.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("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

Linking Your VBA Code To The Search Button

Once you have added your VBA code to your spreadsheet and are ready to see it in action, you will need to turn your search button into a trigger for your macro code.  To do this, simply right-click on your button and select Assign Macro.  The Assign Macro Dialog Box will pop up and you will want to find & select your macro's name (in this case "Searchbox").  Once you have highlighted your macro name and clicked OK, every time you click your search button your filtering macro will run.

Creating A "Clear Filter" Button

You may want to add a feature to clear your filter on your search data. All you need to do is create another button and link the below code to your Clear Filter button

Sub ClearFilter()
'PURPOSE: Clear all filter rules

'Clear filters on ActiveSheet
  On Error Resume Next
    ActiveSheet.ShowAllData
  On Error GoTo 0
  
End Sub

Clear Filter Button

Get The Example File

If you would like to receive the example Excel file I created (shown above in the animated GIFs) you can become a subscriber to my free email newsletters.  All my subscribers receive instant access to all example files posted to TheSpreadsheetGuru.com and they also get emails filled with my exclusive tips & tricks that are not published on this website.  Join the thousands of subscribers and start learning how you can become a guru for your company!

 
     Already Subscribed? Click HERE to log-in to the "Example Files" section

     Already Subscribed? Click HERE to log-in to the "Example Files" section

 

How Do I Modify This To Fit My Specific Needs?

Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it

I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).

I wish you the best of luck and I hope this tutorial gets you heading in the right direction!

Chris "Macro" Newman :)