Amongst the huge amount of information, we often use the method of searching for our desired thing. In terms of working with Microsoft Excel, we can use the Searchable Combo Box. In this article, I am going to explain the whole procedure on the topic of how to create a searchable ComboBox with VBA in Excel. I hope it will be helpful for you if you are looking for a similar sort of thing.
How to Create a Searchable ComboBox with VBA in Excel: Step-by-Step Procedures
In order to create a searchable Combo Box with VBA, I am going to discuss the whole procedure in detail in the following section. For more clarification, I am going to use a dataset with the SL No., Country, and Area columns. Here’s an overview of the dataset for our today’s task.
Step 1: Make Filteration of Data
- As the first step, we need to filter the available data. Input the following formula to do so.
=FILTER(C5:C199,ISNUMBER(SEARCH(Combo Box!B4,C5:C199)),"Not Found")
- After that, press ENTER to have the filtered output on the selected cells.
Step 2: Apply Define Name
- As part of the process, the filtered data needs to be defined. For this, go to the Formulas tab.
- From the ribbon, pick Define Name.
- Now, input a suitable name in the Name section to define.
- Followingly, apply the following formula in the Refers to
=Country!$E$5:$E$199:INDEX(Country!$E$5:$E$199,COUNTIF(Country!$E$5:$E$199,"?*"))- Afterward, click on OK to finish the process.
Step 3: Format a Searchable ComboBox Formation
- In the above section, we arranged everything to apply for VBA. Still, a little bit of arrangement left. For this, go to the Developer tab.
- Next, click on Insert from the ribbon.
- Now, select Combo Box from the ActiveX Controls group.
- Draw a Combo Box with the mouse in your preferred location.
- Now, right-click on the mouse and pick Properties.
- After that, select the Alphabetic tab in the Properties Then make the following changes. Firstly, select False from the AutoWordSelect box. Hence, type B4 in the Linked Celltyping box. Further, select 2 – fnMatchEntryNone from the MatchEntry box.
Step 4: Assign VBA to Searchable ComboBox
- Now, double-click on the Combo Box. A writing space will be available for writing VBA Write the following code in that section.
Private Sub Combo Box1_Change()
Combo Box1.ListFillRange = "Country_Name"
Me.Combo Box1.DropDown
End Sub- Finally, click on Run to finish the process.
- Now, write anything in the search option and you will have the related options in the Combo Box.
Thus, we can create a Searchable ComboBox with Excel VBA.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
At the end of this article, I like to add that I have tried to explain the whole procedure on the topic of how to create a searchable ComboBox with VBA in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.
Related Articles
- How to Select First Item from ComboBox Using VBA in Excel
- How to Use ComboBox to Get Selected Item Utilizing VBA
- Insert ComboBox with Listindex in Excel VBA
- How to Use ListFillRange Property of ComboBox in Excel
- How to Clear Items from VBA ComboBox in Excel
- How to Reset ComboBox to Default Value Using VBA
