I came across a rather interesting requirement. Say I have a drop down but I want to search options with Keyword only.

1.jpg

Keep in mind that the options are in hundreds and I want only relevant to pop up. Say I Type “itil” and relevant options should be listed only.

2.jpg

There are few things we need to do before this will work.

  1. Press Design Mode
  2. Add combo box from Insert
  3. Push the value selected to a cell, P17 in our case.

3.jpg

4. Add the drop down values to Unique sheet.

4.jpg

5. Use name manager to name the fields “DropDownList”, note that its formula based.

5.jpg

6. Now comes the tricky part, This code needs to go into sheet that has Combo Box.

‘—————————————-

Private Sub ComboBox1_GotFocus()
ComboBox1.ListFillRange = “DropDownList”
‘Me.ComboBox1.DropDown

End Sub
Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = “DropDownList”
Me.ComboBox1.DropDown

End Sub

‘—————————————-

6.jpg

You can play around with the code in attached workbook, Also there is a bonus If you press search the required item will be filtered in the sheet.

Drop Down – With Search

There is also some code to find all unique elements in the data.

7.jpg

Cheers

CuriousJatin

Advertisements