Excel has rich resources on filtering as expected from a spreadsheet software. However, the functionality may lack fanciness in a business software, like filtering on-the-fly as users are typing. Fortunately, Excel has enough customization tools to add such a feature easily. In this guide, we're going to show you How to filter as you type with VBA in Excel.
Step 1: Create an Excel Table
The first step is to convert our data into an Excel Table if it isn't already and give it a friendly name. You can convert your data into a table by simply pressing Ctrl + T key combination while a cell is selected on your data or use Insert > Table command in the Ribbon.
Detailed table creation walkthrough: How to Create an Excel Table
Once created, select a cell in the table and navigate to the Table Design tab in Ribbon. Enter a friendly name as Table Name. We chose "Source" in our example.
Step 2: Add a Textbox
You can find the text box control in Developer > Controls > Insert > ActiveX Controls.
If you do not see the Developer tab in the Ribbon, you can add it from Customize Ribbon section in the Excel Options. Check out How to display the Excel Developer tab for using advanced features article to see the full walkthrough.
Once added, select it and check its name on the address bar or in the Properties dialog that you can reach it by right-clicking on the textbox. It is "Textbox1" in our example.
Tip: Whenever you want to make modifications to the textbox, click on the Design Mode toggle under the Developer tab first.
Step 3: VBA part
While Design Mode is on, double click on the textbox. Double-clicking will open the VBA window with Change event of the Textbox1.
All code you enter between "Private Sub TextBox1_Change()" and "End Sub" will run whenever you change the text in the "Textbox1".
You can replace the entire code with the following. However, do not skip to update the names of textbox and table based on your worksheet.
Private Sub TextBox1_Change()
ActiveSheet.ListObjects("Source").Range.AutoFilter Field:=2, Criteria1:="*" & TextBox1 & "*", Operator:=xlFilterValues
End Sub
Once the code is ready, go back to the Excel window and click the Design Mode toggle to turn it off. Then you can seamlessly filter as you type with VBA.
Bonus: Textbox Appearance
You can alter your textbox appearance by changing its properties. For example, you can completely blend the textbox by choosing flat effect, transparent background, and a border.
Tip: Select Categorized in the Properties dialog to see the properties by category.
