Mashhura Jahan
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.
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:
Here, a drop-down menu will appear.
- Thirdly, select Text Box from ActiveX Controls.
- 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.
- After that, Right-click on the Text Box.
- Then, select Properties.
- Next, select a cell in the worksheets that is blank as LinkedCell. Here, I selected cell C4.
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.
- Now, double-click on the Text Box.
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
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.
- 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.
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
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.
Here, I will insert the Option Button.
- Firstly, go to the Developer tab.
- Secondly, select Insert.
- Thirdly, select the Option Button from ActiveX Controls.
- 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.
- After that, insert another Option Button in the same way.
Here, I will change the caption of the Option Button.
- Firstly, Right-click on the Option Button.
- Secondly, select Properties.
- Thirdly, write the Caption as you want it. Here, I changed it to First Letter.
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
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.
- 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.
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.
Here, the Visual Basic window will open.
- Firstly, click on the drop-down menu for UserForm.
- Secondly, select UserForm.
Now, a UserForm will appear.
- Firstly, Right-click on the UserForm.
- Secondly, select Properties.
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.
- Thirdly, change the Caption if you want. Here, I changed mine to search_UserForm.
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.
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.
- 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.
- 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.
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.
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.
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.
- After that, change the Caption as you want.
Here, you can see the Caption has changed.
- After that, 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()
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.
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.
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.
- 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.
i tried example 5 but it did not work
Hello ZAIN,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. For Example 5, you will have to keep 2 things in mind.
1. You will have to change the ColumnCount in the ListBox Properties according to your dataset.
2. In the VBA code, you will have to change Row and Column numbers according to your dataset.
I hope this will help you to solve your problem. If it fails to solve your problem, then please specify where are you facing the problem.
Regards
Mashhura,
ExcelDemy.
hi can you make a tutorial that if we put that search box in another sheet than it will find the keyword/data from another sheet?
Hello ROBYRUBYJANE,
Thank you for reaching out. If I understand correctly, you are interested in creating a search box that can locate values from another worksheet and transfer them to the current worksheet. To accomplish this, you can use the following steps:
Begin by creating a search box, and then add the following code to it.
In this scenario, “Sheet4” is the worksheet containing the search box, and any copied values will be pasted into the range B6:D19 of this worksheet. Prior to pasting, the contents and formatting of the destination range will be cleared. “Sheet6” is the worksheet where the specified keyword will be searched for.
With these steps completed, your search box is now ready for use.
I hope this helps you to achieve your goal. If you need further assistance in this regard, please let us know.
Regards
Zahid
ExcelDemy
hi can i ask how to make a search box that can search data based on date ??
Hello ROBYRUBYJANE,
Thank you for your query. To create a search box that will search data based on a provided date, you can follow the steps given below.
First, construct a search box and add this code to it.
In this code, “Sheet9” refers to the specific worksheet containing the data that requires searching based on dates. The code we have used employs the “greater than or equal” operator, indicating that any data with dates preceding the specified date will not appear in the output.
I hope this answers your question. If you have any more queries, please please reach out to us.
Regards
Zahid
ExcelDemy
What if the range contains not only text but also pictures? Is it still possible to do that?
Hi JHON,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. If you insert pictures with text in the same cell like the following dataset, you will be able to search the pictures easily by following the first method from this article.
In the following image, you can see that the results for the router are displayed with pictures.
For this case, you will have to set the Properties for the pictures as Move and size with cells from the Format Graphic task pane.
I hope this will help you to solve your problem. Please let us know if you have other queries.
Regards
Mashhura Jahan
ExcelDemy.