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 informationOpen the "Prices" Excel fileAn input box requests the search textThe 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.

Andy L Gibson is a former Web Site programmer rediscovering his interest in software applications for small business.

Previously he has used AJAX, XML and ASPX to program Web Sites for restaurants, auction houses and Heath Authorities. He is keen to answer questions and explore business opportunities whereever they might present themselves. You can talk with Andy at his blog at http://solutions4business.wordpress.com/ including links to other articles on VBA issues he has written.

View the original article here