Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Create a Searchable ComboBox with VBA in Excel

Amongst the huge amount of information, we often use the method 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.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Step-by-Step Procedures to Create a Searchable ComboBox with VBA in Excel

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.

Searchable Combobox in Excel VBA


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")

Make Filteration of Data

  • 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.

Searchable Combobox in Excel VBA

  • 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.

Searchable Combobox in Excel VBA

  • Draw a Combo Box with the mouse in your preferred location.

  • Now, right-click on the mouse and pick Properties.

Searchable Combobox in Excel VBA

  • 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.

Read More: How to Add ComboBox in Excel (3 Easy Methods)


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.

Assign VBA to Searchable Combo Box

  • Now, write anything in the search option and you will have the related options in the Combo Box.

Searchable Combobox in Excel VBA

Thus, we can create a Searchable ComboBox with Excel VBA.

Read More: How to Use VBA to Populate ComboBox List from Range in Excel


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

Naimul Hasan Arif

Naimul Hasan Arif

Hello, I am Arif. I am an Engineer who graduated from BUET. I want to pursue an enterprising career in a progressive environment where my skills & knowledge can be enhanced to their maximum potential to contribute to the overall success and growth of the organization.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo