The Find All Form for Excel allows you to type a search query in a text box and have the results appear in a list box. The results are narrowed down as you type. This is similar functionality to Google's auto fill or auto suggest feature when doing a Google search. See the animated screen capture below.
This is a very basic application that mimics Excel's built-in Find All feature. The code uses Chip Pearson's FindAll Function in combination with the KeyUp Event in the text box to return results in the list box as you type. A sample workbook with all the code is available for free download below.
The features and capabilities can be greatly expanded to meet your needs. I've used this in an add-in that runs searches on general ledger (GL) account codes. The user can quickly search for account codes from the GL or database, and quickly insert them into the worksheet. In my application, the tables containing the account codes are stored in the add-in workbook. I've also developed additional processes to automatically update the GL tables as they are updated in the system. This makes the searches extremely fast since the underlying source data is stored in the add-in.
What could you use this tool for? Leave a comment below.
Find All VBA Form.xls (464.9 KB)
File Find All VBA Form (Results on Form).xls (474.6 KB)
The “All Sheets” file above searches all the sheets in the file. The sheet name is included in column 2 of the results along with the cell address. When an item is clicked in the results, the resulting sheet and cell are selected.
The “Copy Paste” file above pastes the clicked item in a list on a Results Sheet. This allows you to track which items the user clicks on in the results list on the form.