Create a Search Box in Excel with VBA (5 Suitable Examples)

If you are looking for a way to create a search box in Excel with VBA, then this article will be beneficial to you. The focus of this article is to explain how to create a search box in Excel with VBA.


Download Practice Workbook


5 Suitable Examples to Create a Search Box in Excel with VBA

To explain this article, I have taken the following table as the dataset. This table contains Sate, Sales Person, and Product. I will create a search box in Excel with VBA for this table. Here, I will explain 5 suitable examples.

Create a Search Box in Excel with VBA


Example-01: Employing Text Box Feature to Create a Search Box in Excel

In this example, I will explain how you can create a search box in Excel with VBA to find the results that start with the searched letter. Let’s see the steps.

Steps:

  • Firstly, go to the Developer tab.
  • Secondly, select Insert.

Here, a drop-down menu will appear.

  • Thirdly, select Text Box from ActiveX Controls.

Employing Text Box Feature to Create a Search Box in Excel

  • After that, click and drag your mouse cursor where you want your Text Box.

Now, you will see your Text Box is inserted into your worksheet.

Inserting Text Box to Create a Search Box in Excel With VBA

  • After that, Right-click on the Text Box.
  • Then, select Properties.

Changing Properties of Text Box to Create a Search Box in Excel with VBA

  • Next, select a cell in the worksheets that is blank as LinkedCell. Here, I selected cell C4.

Linking Cell to Text Box to Create a Search Box in Excel with VBA

Here, I will name the table because I will need the table name to write the VBA code.

  • Firstly, select any cell from the table. Here, I selected cell B6.

  • Secondly, go to the Table Design tab.
  • Thirdly, name the table as you want. Here, I named it TextBox_Data.

Giving Table a Name to Create a Search Box in Excel with VBA

  • Now, double-click on the Text Box.

Opening Module to Create a Search Box in Excel with VBA

After that, a Module will open with a Private Sub on it.

  • Next, write the following code in that Module.
Private Sub TextBox1_Change()
Application.ScreenUpdating = False
ActiveSheet.ListObjects("TextBox_Data").Range.AutoFilter field:=2, Criteria1:=[c4] & "*", Operator:=xlFilterValues
Application.ScreenUpdating = True
End Sub

VBA Code in Text Box to Create a Search Box in Excel with VBA

Code Breakdown

  • Here, a Private Sub Procedure was already created by the Text Box. A Private Sub is only applicable for that specific sheet it is on.
  • Then, I set Application.ScreenUpdating property as False to speed up the macro.
  • After that, I used the ListObjects property to return the table named “TextBox_Data”.
  • Next, I used Range.AutoFilter method to filter the range. Here, I selected 2 for field which will filter the 2nd column of the table and I selected [C4] & “*” as Criteria1 which will match the 1st letter with the input in Search Box. For Operator, I selected xlFilterValues which will filter the values.
  • Then, I set Application.ScreenUpdating property as True because I have reached the last part of my macro.
  • Finally, I ended the Sub Procedure.

Now, save the code, and go back to the worksheet. Here, you will see a filter is added to the Sales Person column.

Adding Filter to Create a Search Box in Excel with VBA

  • Finally, you can search in the Search Box whatever you want. Here, I searched for “a” and the names that start with “a” have appeared.


Example-02: Creating a Search Box in Excel VBA by Using a Variable

For this example, I will use a variable to create a Search Box in Excel with VBA. Let’s see the steps.

Steps:

  • To begin with, insert a Text Box and link it to a cell by following the procedure from Example-01.
  • After that, double-click on the Text Box.

Creating a Search Box in Excel VBA by Using a Variable

Now, a Module will open with a Private Sub on it.

  • Next, write the following code in that Module.
Private Sub TextBox1_Change()
Dim search_str As String
search_str = "*" & [c4] & "*"
Debug.Print search_str
ActiveSheet.ListObjects("DeclaringVariable_Data").Range.AutoFilter _
field:=1, _
Criteria1:=search_str, _
Operator:=xlFilterValues
End Sub

VBA Code with Variable to Create a Search Box in Excel

Code Breakdown

  • Here, a Private Sub Procedure was already created by the Text Box. A Private Sub is only applicable for that specific sheet it is on.
  • Then, I declared a variable named search_str as String.
  • After that, I set  search_str as “*” & [c4] & “*”.
  • After that, I used the ListObjects property to return the table named “DeclaringVariable_Data” from the ActiveSheet.
  • Next, I used Range.AutoFilter method to filter the range. Here, I selected 1 for the field which will filter the 1st column of the table and I selected search_str as Criteria1 which will match the letter from the 1st column with the input in the Search Box. For Operator, I selected xlFilterValues which will filter the values.
  • Finally, I ended the Sub Procedure.

At this point, save the code and go back to your worksheet. Here, you will see a filter is added to the column named State.

  • Now, you can search in the Search Box whatever you want. Here, I searched for “oh”, and the State that contains “oh” appeared.

Read More: How to Create a Search Box in Excel Without VBA (2 Easy Ways)


Example-03: Using Text Box with Option Buttons

In this example, I will create a Search Box in Excel with VBA with two Option Buttons to search for 2 different situations. One will filter if the value contains the searched letter and the other will filter if the searched letter matches the first letter of the value.

Let’s see the steps.

Steps:

  • To begin with, insert a Text Box and link it to a cell by following the procedure from Example-01.

Using Text Box with Option Buttons

Here, I will insert the Option Button.

  • Firstly, go to the Developer tab.
  • Secondly, select Insert.
  • Thirdly, select the Option Button from ActiveX Controls.

Inserting Option Button to Create a Search Box in Excel with VBA

  • After that, click and drag your mouse cursor where you want the Option Button.

Now, you will see an Option Button is inserted into your Excel sheet.

Option Button to Create a Search Box in Excel with VBA

  • After that, insert another Option Button in the same way.

Inserting Another Option Button to Create a Search Box in Excel with VBA

Here, I will change the caption of the Option Button.

  • Firstly, Right-click on the Option Button.
  • Secondly, select Properties.

Changing Properties of Option Button to Create a Search Box in Excel with VBA

  • Thirdly, write the Caption as you want it. Here, I changed it to First Letter.

Changing Caption of Option Button to Create a Search Box in Excel with VBA

Now, you can see the Caption has changed.

  • After that, change the caption of the other Option Button in the same way. Here, I changed it to Contains Letter.

  • At this point, double-click on the Text Box.

Here, a Module will open with a Private Sub on it.

  • After that, write the following code in the Module.
Private Sub TextBox1_Change()
If OptionButton1 Then
    ListObjects("OptionButtons_Data").Range.AutoFilter field:=2, Criteria1:=[D4] & "*", Operator:=xlFilterValues
Else
    ListObjects("OptionButtons_Data").Range.AutoFilter field:=2, Criteria1:="*" & [D4] & "*", Operator:=xlFilterValues
End If
End Sub

Using IF staement in VBA Code to Create a Search Box in Excel

Code Breakdown

  • Here, a Private Sub Procedure was already created by the Text Box. A Private Sub is only applicable for that specific sheet it is on.
  • Then, I used the IF Statement to give different results in two different situations. If OptionButton1 is selected then Criteria1 is selected as [D4] & “*” for the Range.AutoFilter method. This means It will filter the values that start with the searched letter. Else, Criteria1 is selected as “*” & [D4] & “*” for the Range.AutoFilter method. That means It will filter the values that contain the searched letter.
  • After that, I ended the IF statement.
  • Finally, I ended the Sub Procedure.

Now, save the code and go back to your worksheet.

  • After that, double-click on the Option Button.

Here, another Private Sub Procedure will be created in the same Module.

Private Sub Procedure Created by Option Button to Create a Search Box in Excel with VBA

  • Now, write the following code in the Module.
Private Sub OptionButton1_Click()
Call TextBox1_Change
End Sub

Code Breakdown

  • Here, a Private Sub Procedure was already created by the OptionButton1. A Private Sub is only applicable for that specific sheet it is on.
  • Then, I used the Call statement to call the Sub Procedure named TextBox1_Change.
  • After that, I ended the Sub Procedure.

Now, save the code and go back to your worksheet again.

  • Next, double-click on Option Button named Contains Letter.

Here, another Private Sub Procedure will be created in the same Module.

  • After that, write the following code in the Module.
Private Sub OptionButton2_Click()
Call TextBox1_Change
End Sub 

Code Breakdown

  • Here, a Private Sub Procedure was already created by the OptionButton2. A Private Sub is only applicable for that specific sheet it is on.
  • Then, I used the Call statement to call the Sub Procedure named TextBox1_Change.
  • After that, I ended the Sub Procedure.

Finally, save the code and return to your worksheet.

  • Now, you can search in the Search Box whatever you want and select your preferred Option Button. Here, I searched for the letter “m” and selected the First Letter. And the names that start with “m” are filtered.

In the following image, you can see that I have now selected the Option Button named Contains Letter and the names that contain the letter “m” are filtered.

Read More: How to Create a Filtering Search Box for Your Excel Data (5 Ways)


Example-04: Use of VBA UserForm to Create Search Box

Here, I will explain another example. To explain this example, I have taken the following dataset which is not a table it is a range of data. For this example, I use the UserForm. Let’s see how you can create a Search Box in Excel with VBA.

Use of VBA UserForm to Create Search Box


Step-01: Creating and Modifying UserFrom

In this first step, I will create the UserForm and modify it according to my need.

  • Firstly, go to the Developer tab.
  • Secondly, select Visual Basic.

Opening Visual Basic Window to Create a Search Box in Excel with VBA

Here, the Visual Basic window will open.

  • Firstly, click on the drop-down menu for UserForm.
  • Secondly, select UserForm.

Opening UserForm to Create a Search Box in Excel with VBA

Now, a UserForm will appear.

  • Firstly, Right-click on the UserForm.
  • Secondly, select Properties.

Changing Properties of UserForm to Create a Search Box in Excel with VBA

Now, Properties for UserForm1 will appear on the left side of the screen. Here, I will change the BackColor of the UserForm.

  • Firstly, select the drop-down option from BackColor.
  • Secondly, select the color you want for your background color.

Changing Background Color of the UserForm to Create a Search Box in Excel with VBA

  • Thirdly, change the Caption if you want. Here, I changed mine to search_UserForm.

Changing Caption of UserForm to Create a Search Box in Excel with VBA

Now, I will change the Height and the Width of the UserForm.

  • Firstly, go to the Categorized tab.
  • Secondly, change the Height as per your preference.
  • Thirdly, change the Width as per your preference.

  • After that, select Label from the Toolbox.
  • Next, click and drag your mouse cursor where you want the Label.

Here, you will see the Label is inserted.

  • Next, select ComboBox from the Toolbox.
  • After that, click and drag your mouse cursor where you want your ComboBox.

Inserting ComboBox to Create a Search Box in Excel with VBA

Here, you can see the ComboBox is inserted into the UserForm.

  • Now, select Label from Toolbox again.
  • After that, click and drag your mouse cursor where you want the Label.

Now, you will see the Label is inserted.

Inserting Label in UserForm to Create a Search Box in Excel with VBA

  • Here, select TextBox from the Toolbox.
  • Then, click and drag your mouse cursor where you want the TextBox.

Here, you will see that TextBox is inserted into the UserForm.

Inserting TextBox in UserForm to Create a Search Box in Excel with VBA

  • Now, select all 4 of them together.

  • Then, change the Height from Properties as you want. Here, I changed mine to 24.

  • After that, Right-click on any of the boxes.
  • Next, select Align.
  • Finally, select Tops.

Changing Alignment of the Boxes to Create a Search Box in Excel with VBA

Here, you can see all of the boxes have the same height and they are aligned at the top of the sheet.

  • Now, select Label1.

  •  After that, change the Caption from the Properties. Here, I change mine to Field.

  • Now, click on the drop-down option for Font.

After that, a dialog box will appear.

  • Next, select Font Size as you want.
  • Then, select OK.

You can also change the Font and Font Style from here.

Now, I will change TextAlign.

  • Firstly, click on the drop-down option for TextAlign.
  • Secondly, fmTextAlignCenter from the drop-down menu.

Here, you can see the Caption of Label1 has changed.

  • Now, change the Caption of Label2 in the same way. Here, I have changed mine.

  • After that, select ListBox from the Toolbox.

  • Next, click and drag the mouse cursor where you want the ListBox.

 

Finally, I have inserted everything I wanted in my UserForm.


Step-02: Writing VBA Code

In this step, I will write the VBA code to create a Search Box in Excel by using UserForm.

  • Firstly, double-click on anywhere in the UserForm.

Writing VBA Code

Now, a module for UserForm1 will open with a Private Sub Procedure on it.

  • Firstly, click on the marked drop-down option.
  • Secondly, select Initialize.

  • After that, delete the first Private Sub Procedure.
  • Then, write the following code in the UserForm.
Private Sub UserForm_Initialize()
Dim col_no As Integer

For col_no = 2 To 4
Me.ComboBox1.AddItem ActiveSheet.Cells(4, col_no).Value
Next

With Me.ListBox1
    .ColumnCount = 3
    .ColumnWidths = "90;90;90"
End With

End Sub

Code Breakdown

  • Here, a Private Sub Procedure was already created by the UserForm. A Private Sub is only applicable for that specific sheet it is on.
  • Then, I declared a variable named col_no as Integer.
  • After that, I used a For Next Loop to go through columns 2 to 4.
  • Next, I used the Me keyword to make it behave like an implicitly declared variable. Then, I used ComboBox1.AddItem method to add the headers of the table to the ComboBox.
  • After that, I used a With Statement to define the ColumnCount and ColumnWidths in the ListBox1.
  • Then, I ended the With Statement.
  • Finally, I ended the Sub Procedure.

After that, save the code and go back to the UserForm.

  • Now, double-click on the ComboBox.

Opening Private Sub for ComboBox to Create a Search Box in Excel with VBA

Here, another Private Sub will be created in the UserForm.

  • After that, write the following code in the UserForm.
Dim field

Private Sub ComboBox1_Change()
Dim col_no As Integer
Dim col_headers
col_headers = Array("B", "C", "D")

For col_no = 2 To 4
If ActiveSheet.Cells(4, col_no).Value = Me.ComboBox1.Value Then
field = col_headers(col_no - 2)
End If
Next

Me.ListBox1.Clear
Me.TextBox1.Value = ""
Me.TextBox1.SetFocus

End Sub

Code Breakdown

  • Here, I declared a variable named Field.
  • Next, a Private Sub Procedure was already created by the UserForm. A Private Sub is only applicable for that specific sheet it is on.
  • Then, I declared a variable named col_no as Integer.
  • After that, I declared another variable named col_headers.
  • Next, I used the Array function to assign an array as col_headers.
  • After that, I used a For Next Loop to go through columns 2 to 4.
  • Then, I used an IF statement to check if the value in the cell matches the value in ComboBox1. If it matches then the statement will return the field as col_headers(col_no – 2) which will be the column that will be filtered.
  • Next, I ended the IF Statement.
  • Then, I used ListBox1.Clear to remove all data from ListBox1. Here, I used the Me keyword to make it behave like an implicitly declared variable.
  • After that, I used TextBox1.Value to specify the text in the text box. And Set it as blank. I also used the Me keyword to make it behave like an implicitly declared variable.
  • After that, I used TextBox1.SetFocus method to move focus to this specified form.
  • Finally, I ended the Sub Procedure.

Now, save the code and return back to the UserForm.

  • After that, double-click on the TextBox.

Here, another Private Sub Procedure will be created.

  • Now, write the following code in the UserForm.
Private Sub TextBox1_Change()
On Error Resume Next

If Me.TextBox1.Text = "" Then
Me.ListBox1.Clear
Exit Sub
End If

Me.ListBox1.Clear
Dim row_no As Integer
Dim last_row_no As Integer
last_row_no = ActiveSheet.Range("B100").End(xlUp).Row

For row_no = 5 To last_row_no
    letter = Len(Me.TextBox1.Text)
    If UCase(Left(ActiveSheet.Cells(row_no, field).Value, letter)) = UCase(Me.TextBox1.Text) Then
    With Me.ListBox1
    .AddItem ActiveSheet.Cells(row_no, "B").Value
    .List(.ListCount - 1, 1) = ActiveSheet.Cells(row_no, "C").Value
    .List(.ListCount - 1, 2) = ActiveSheet.Cells(row_no, "D").Value
    End With
    End If
Next

End Sub

Code Breakdown

  • Here, a Private Sub Procedure was already created by the UserForm. A Private Sub is only applicable for that specific sheet it is on.
  • After that, I wrote On Error Resume Next to continue to code if any error occurs.
  • Then, I used an IF statement to check if the TextBox is blank. If it is blank then the ListBox1 will be cleared.
  • After that, I ended the IF statement.
  • Then, I declared a variable named row_no as Integer, and another variable named last_row_no as Integer.
  • Next, I used the Range.End(xlUp) property to find the last non-blank row number.
  • After that, I used a For Next Loop to go through all the rows in a column.
  • Then, I used another IF statement to see if the text in the TextBox matches the value in the cell. Here, I used the Left function to match from the beginning of the text. I also used the UCase function to convert the letter to an uppercase letter.
  • Next, I used the With statement to add the item to the ListBox and make a list with the other 2 columns, if the two values match.
  • Then, I ended the With statement.
  • After that, I ended the IF statement.
  • Finally, I ended the Sub Procedure.
  • Now, save the code and go back to your worksheet.

Step-03: Inserting Command Button

In this 3rd step, I will insert a Command Button that will help to show the UserForm.

  • Firstly, go to the Developer tab.
  • Secondly, select Insert.

Here, a drop-down menu will appear.

  • Thirdly, select CommandButton from the ActiveX Controls.

  • After that, click and drag your mouse cursor where you want your CommandButton.

Now, you will see the CommandButton is inserted into the worksheet.

  • Firstly, Right-click on the CommandButton.
  • Secondly, select Properties.

Changing Properties of CommandButton to Create a Search Box in Excel with VBA

  • After that, change the Caption as you want.

Here, you can see the Caption has changed.

  • After that, double-click on the CommandButton.

Opening Private Sub for the CommandButton to Create a Search Box in Excel with VBA

Now, a module will open with a Private Sub on it.

  • After that, write the following code in the module.
Private Sub CommandButton1_Click()
UserForm1.Show
End Sub

Code Breakdown

  • Here, a Private Sub Procedure was already created by the CommandButton. A Private Sub is only applicable for that specific sheet it is on.
  • Then, I used the Show method to display the UserForm1.
  • After that, I ended the Private Sub Procedure.

Finally, save the code and go back to your worksheet.


Step-04: Using the Search Box

In this step, I will explain how you can use the search box you created in the UserForm.

  • Firstly, click on the CommandButton.

Using the Search Box

Here, the UserForm will appear.

  • After that, select the drop-down option for selecting Field.
  • Then, select the Field from the drop-down menu. Here, I selected State.

  • Now, search for whatever you want. Here, I searched for “c” and the States that start with “c” have appeared.

Read More: How to Create a Search Box in Excel for Multiple Sheets (2 Ways)


Example-05: Applying ListBox to Create a Search Box

In this method, I will show an example of how you can create a search box in Excel with VBA by using ListBox which will filter every value that contains the searched letters. Let’s see the steps.

Steps:

  • Firstly, go to the Developer tab.
  • Secondly, select Visual Basic.

Applying ListBox to Create a Search Box

Now, the Visual Basic window will open.

  • Firstly, click on the drop-down menu for UserForm.
  • Secondly, select UserForm.

Here, a UserForm will appear.

  • Now, Right-click on the UserForm.
  • After that, select Properties.

Now, Properties for UserForm2 will appear on the left side of the screen. Here, I will change the BackColor of the UserForm.

  • Firstly, select the drop-down option from BackColor.
  • Secondly, select the color you want for your background color.

  • After that, change the Caption if you want. Here, I changed mine to Search Criteria.

Here, I will change the Height and the Width of the UserForm.

  • Firstly, go to the Categorized tab.
  • Secondly, change the Height as per your preference.
  • Thirdly, change the Width as per your preference.

  • Now, select TextBox from the Toolbox.

  • After that, click and drag your mouse cursor where you want the TextBox.

Finally, you will see that TextBox is inserted.

  • Now, select ListBox from the Toolbox.
  • Next, click and drag the mouse cursor where you want the ListBox.

Finally, I have inserted everything I wanted in my UserForm.

  • After that, Right-click on the ListBox.
  • Then, select Properties.

Changing Properties of ListBox to Create a Search Box in Excel with VBA

  • Now, go to the Categorize tab from the Properties for ListBox1.
  • Then, select ColumnCount as your column number in the table. Here, I selected 3 because my table has 3 columns.

  • After that, double-click on the TextBox.

Here, a UserForm will open with a Private Sub.

  • Now, write the following code in the UserForm.
Private Sub TextBox1_Change()
Dim row_no As Long
Me.TextBox1 = Format(StrConv(Me.TextBox1, vbLowerCase))
Me.ListBox1.Clear
Me.ListBox1.AddItem "Sales Person"
Me.ListBox1.List(0, 1) = "State"
Me.ListBox1.List(0, 2) = "Product"
Me.ListBox1.Selected(0) = True

For row_no = 5 To ActiveSheet.Range("B100").End(xlUp).Row
For sr_for = 1 To Len(ActiveSheet.Cells(row_no, 3))

letter = Me.TextBox1.TextLength
If LCase(Mid(ActiveSheet.Cells(row_no, 3), sr_for, letter)) = Me.TextBox1 And Me.TextBox1 <> "" Then
Me.ListBox1.AddItem ActiveSheet.Cells(row_no, 3)
Me.ListBox1.List(ListBox1.ListCount - 1, 1) = ActiveSheet.Cells(row_no, 2)
Me.ListBox1.List(ListBox1.ListCount - 1, 2) = ActiveSheet.Cells(row_no, 4)
End If

Next sr_for
Next row_no

End Sub

Code Breakdown

  • Here, a Private Sub Procedure was already created by TextBox. A Private Sub is only applicable for that specific sheet it is on.
  • Then, I declared a variable named row_no as Long.
  • Next, I used the Format function to format the text in TextBox. In the Format function, I used the StrConv function to convert the text to lowercase.
  • Then, I used ListBox1.Clear to remove all data from ListBox1. Here, I used the Me keyword to make it behave like an implicitly declared variable.
  • After that, I used ListBox1.AddItem to add a new Item to the ListBox. Which is the column header of the table.
  • Next, I used ListBox1.List to get the other two column headers.
  • Then, I used a For Next Loop to go through all the rows of the table. Then, I used another For Next Loop to go through the full length of the string. Here, I used the Len function to return the number of characters.
  • After that, I used another IF statement to see if the text in the TextBox matches the value in the cell. Here, I used the Mid function to match from anywhere in the text. I also used the LCase function to convert the letter to a lowercase letter.
  • Then, I used ListBox1.AddItem method to add the item to the ListBox if the two values match. And then, make a list with the other 2 columns by using ListBox1.List.
  • Next, I ended the IF statement.
  • Finally, I ended the Sub Procedure.

Now, save the code, and go back to your worksheet.

  • After that, insert a CommandButton by following the procedure from Step-03 of Example-04.
  • Next, double-click on the CommandButton.

Now, a module will open with a Private Sub on it.

  • After that, write the following code in the module.
Private Sub CommandButton1_Click()
UserForm2.Show
End Sub

Code Breakdown

  • Here, a Private Sub Procedure was already created by the CommandButton. A Private Sub is only applicable for that specific sheet it is on.
  • Then, I used the Show method to display the UserForm2.
  • After that, I ended the Private Sub Procedure.

Finally, save the code and go back to your worksheet.

  • Now, click on the CommandButton.

Here, the UserForm will open.

  • After that, you can search for whatever you want. Here, I searched for “an” and the Sales Person names that contain “an” have appeared.


Things to Remember

  • Whenever working with VBA in Excel, it should be kept in mind that you must save the Excel file as Excel Macro-Enabled Workbook otherwise the macros won’t work.

Practice Section

Here, I have provided a practice sheet for you to practice how to create a search box in Excel with VBA.


Conclusion

To conclude, I tried to cover how to create a search box in Excel with VBA in this article. I explained 5 different and suitable examples. I hope this was helpful for you. To get more articles like this visit ExcelDemy. If you have any questions, let me know in the comment section below.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo