How To Use A VBA Input Box To Create A Customized Excel Search Function

If you use Excel to store data, sometimes you need to search for information quickly and efficiently.
For example if you take a call from an important customer you might need to access product or pricing details.
A few lines of VBA code can create an input box which you can use to search for information in a worksheet.
A typical scenario might go something like this:

• Answer an incoming call and a customer requests price information
• Open the "Prices" Excel file
• An input box requests the search text
• The VBA code highlights any items found matching the query

Creating The VBA Search Code We want the input box to activate as soon as the file opens so we place the code in the workbook section of the VBA code window, not as a general module.

private sub workbook_open() ' the rest of the code goes here end sub The first thing we'll do is create an inputbox where a user can enter the search term.
You can customize the input box with prompts and a title, but we'll stick to a standard format:
txt = InputBox("Search") Next, we'll activate the worksheet which holds the searchable data, clear any previous search results and define the range to search.

Sheets("search").
Activate Cells.
ClearFormats Range("a1").
CurrentRegion.
Select The code now has a search string to look for, and a data range to search through.
We'll look for lower case versions of the data and the text so that a user doesn't have to worry about matching case.

For Each c In Selection If we find a match we want to be able to highlight the entry; in this scenario we'll highlight the cells from the first column to the last column of data rather than the entire row.
The advantage in using the instr function is that a partial match can be found, saving the user precious time.
Typically, when searching for "widget" experience might tell the user the best approach is to simply type "widg" in the search box.

If InStr(LCase(c.
Text), LCase(txt)) Then first = Range(c.
Address).
End(xlToLeft).
Address last = Range(c.
Address).
End(xlToRight).
Address Range(first & ":" & last).
Interior.
ThemeColor = xlThemeColorDark2 End If Next Range("A1").
Select The code can be edited to search a specific column or worksheet and it would be possible to search all the sheets in a workbook with a little extra coding.
Summary This code is an example of how some innovation can dramatically improve business processes, in this case finding information quickly and efficiently.
And while Excel has many built-in functions to achieve the same result, a little knowledge of VBA combined with your own business knowledge can bring dramatic workplace improvements.