I created a combobox of which the list of options updates whenever you type in the box. Just a bit like entering a search in google works with suggestions. I followed this tutorial to do so: https://trumpexcel.com/excel-drop-down-list-with-search-suggestions/
There are 4 columns of data in the sheet that make this work, combined with a named range, and a short piece of VBA to update the combobox. The combobox is in linked to cell B3. And data is in E:H starting at row 3.
- First column includes the options for the combobox. There are 5619 possible options to choose from.
- Second column has the following formula to determine if the entry of the combobox is in the text of the same row in the first column:
=--ISNUMBER(IFERROR(SEARCH($B$3,E3,1),""))
- Third column has the following formula to establish the amount of matches found:
=IF(F3=1,COUNTIF($F$3:F3,1),"")
- Fourth column has the following formula to return the list of possible options based on the value typed in the combobox:
=IFERROR(INDEX($E$3:$E$22,MATCH(ROWS($G$3:G3),$G$3:$G$22,0)),"")
The code to update the combobox is as follows:
Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "DropDownList"
Me.ComboBox1.DropDown
End Sub
With DropDownList
Being a named range that selects all the names in the fourth column.
The problem I face here is purely performance. It is really slow and unresponsive on a PC. Let alone deployed on a mobile device, which I intend to.
The problem is ofcourse within the fact that there are 5600+ options for the combobox, and therefore 4*5600 formula's to be recalculated at every character entered in the combobox. I was wondering if there is a way to do with less helper columns, and maybe more VBA to reduce the calculation times required?