Excel Table Search While Typing – Using VBA and TextBox (Macro-Enabled)

📘 Introduction

In this tutorial, you will learn how to search a table in Microsoft Excel using a TextBox while typing. This guide includes the complete VBA code, explains how to save your file as a .xlsm, and how to send it to others without macro security alerts.

🛠️ What You Need

• Microsoft Excel (2013 or later)
• Developer Tab enabled

• Macro-enabled file (.xlsm)

🚀 Step-by-Step Guide

🔹 Step 1: Prepare Your Data Table

Create a simple table in Excel and name it SearchTable.

+------------+----------+
| FirstName  | Country  |
+------------+----------+
| Ahmed      | Egypt    |
| Sarah      | Canada   |
| John       | USA      |
+------------+----------+

🔹 Step 2: Add a TextBox Control

Enable the Developer Tab → Insert → ActiveX Controls → TextBox. Name it TextBox1.

🔹 Step 3: Add the VBA Code

Press ALT + F11 to open the VBA editor, then double-click the worksheet (e.g., Sheet1) and paste the following code:

Private Sub TextBox1_Change()
    Dim cell As Range
    Dim found As Boolean
    Dim searchValue As String

    searchValue = Me.TextBox1.Text
    found = False

    ' Reset filter
    Me.ListObjects("SearchTable").Range.AutoFilter Field:=1

    If Len(searchValue) > 0 Then
        On Error Resume Next
        Me.ListObjects("SearchTable").Range.AutoFilter Field:=1, Criteria1:="*" & searchValue & "*"
        On Error GoTo 0
    End If
End Sub

Note: Replace Field:=1 with the correct column index to search in.

💾 Step 4: Save as Macro-Enabled Workbook

Save your Excel file as .xlsm (Macro-Enabled Workbook):

• File → Save As
• Choose Excel Macro-Enabled Workbook (*.xlsm)

✉️ Sharing with Others

To avoid security prompts:

• Send the file compressed in a .zip format
• Ask users to unblock the file:
  1. Right-click the .xlsm file → Properties
  2. Check “Unblock” and click Apply

🔐 Macro Security Settings

Ensure the recipient enables macros by going to:

• File → Options → Trust Center → Trust Center Settings
• Macro Settings → Enable all macros (not recommended permanently)

📎 Download Sample Workbook

Click here to download the demo file

---

📌 Summary

• Use VBA to live-search a table via TextBox
• Save your work as .xlsm
• Share securely by zipping and unblocking

🧠 Related Posts

• All VBA Articles
• Download Excel Templates

This guide is part of our Accounting in Excel Series. Subscribe to get more tutorials and templates delivered weekly.