I’ve been having a great deal of difficulty with a method I found online for generating a search bar with suggestions (like a google search) within an excel spreadsheet. I’m hoping the problem lies with the underlying VBA and also that someone on here might know something that can help.
The method is described here; http://trumpexcel.com/2013/10/…-with-search-suggestions/
I’m extremely keen to use this method not only on the project I’m working on at present but also on a number of others I’m looking to work on down the track.
I have integrated the method into my current project (attached) but the combo box seems to be launching the drop down list sporadically when I use other parts of the spreadsheet.
More specifically if I search a product using the search bar (located at cell H5 of the Search sheet), select one of the products in the drop down list and then do anything elsewhere in the sheet (enter something into another cell, select a checkbox, click a button, etc.), the dropdown list will dropdown again even through I’m not typing in the combo box.
Ideally the dropdown list of options will only drop down when someone is typing in the combo box and has not yet selected an item. Once an item is selected from the dropdown list it should not dropdown again until that item is cleared from the dropdown box and a new search is made.
It should function as in the GIF at the top of the original method
To rectify this issue I've tried the following:
- I’ve been unable to get in contact with the original designer
- I’ve been discussing it with a very knowledgeable forum moderator from the excel forum, alas we’re both stumped.
- I’ve tried the spreadsheet on numerous computers and with several versions of excel all to no avail.I’ve asked on the excel help forum also to no avail: http://www.excelforum.com/exce…dropdown-misbehaving.html
- I found a workaround (or rather the forum moderator found a work around) however it is behaving similarly and isn’t as simple as the original method.
Has anyone any idea why it might be doing this? Could it perhaps be clashing with another code or have something to do with the version of excel I'm using (2010)?
The only solution I can think of at this point is to add code of some kind to dismiss the dropdown list automatically whenever it drops down and is not active but that seems like a sloppy workaround rather than a permanent solution (and I have no idea whether or not it’s even possible).
Would appreciate any insight anyone would have.
P.S. The helper columns required for the method can be found on the ‘Product Database’ sheet flanking the 'Entry Number' column.