Preventing Combo Box drop down list from activating when not in use

  • G'day OzGrid


    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.


    Regards


    MattRNR


    P.S. The helper columns required for the method can be found on the ‘Product Database’ sheet flanking the 'Entry Number' column.

  • Re: Preventing Combo Box drop down list from activating when not in use


    Hi MattRNR,


    Welcome to the Ozgrid forum.


    The problem, I believe, relates to a chain of events, starting with the formula within the [H4] field of the Product_Database table, and ending with the ComboBox1 event code.


    The formula for the [H4] field contains the OFFSET function. OFFSET is a 'volatile' function, which recalculates on any Excel recalculation (as happens when you change any cell). This forces the Product_Database table to change. The named range 'DropDownList' is built using the Product_Database table, so that too changes. 'DropDownList' is used as the ListFillRange for ComboBox1, so the change invokes the ComboBox1_Change event code. The event code contains the command .Dropdown to display the list.


    If you change the [H4] formula to avoid using OFFSET, you should avoid the problem.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Preventing Combo Box drop down list from activating when not in use


    I'd clear the ListFillRange property, then change the worksheet code to:


    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Preventing Combo Box drop down list from activating when not in use


    Given that [H3] is also used in the definition of the DropDownList range name, then it would probably be a good idea, but it might depend on which solution you opt for. As Rory's solution only populates the list at the point that you click into the ComboBox, it avoids the problem affecting the drop-down at other times.


    With regard to replacing the formulas for [H3] and [H4], if that's what you decide to do, without knowing exactly what you are using the Product_Database columns for, it does seem that the formulas are a little over-complicated. Even if you opt for Rory's solution, it might be a good idea to avoid using OFFSET, as the recalculation will still happen every time you change a cell. Depending on the size of your live table, that might or might not represent a problem. Using the field names in conjunction with INDEX and/or MATCH would probably suffice.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Preventing Combo Box drop down list from activating when not in use


    G’day again guys

    Sorry for the slow reply, busy season

    I’ve attempted both of these solutions

    Batman: I have replaced the formulas in H3 and H4 to eliminate the OFFSET function however the dropdown list issue remains unchanged (see attached), could I have inadvertedly replaced one volatile function with another?

    OzMVP: your solution resolved the issue of the list dropping down, but no longer gives me a subset of the values as I’m typing (see attached). Am I doing something wrong?

    Any ideas?

    Regards
    MattRNR

  • Re: Preventing Combo Box drop down list from activating when not in use


    As I haven't been able to fully resolve the problem with my approach, due to time available, I thought I'd best update you on progress.


    To try to determine what order events are happening in, I added a Debug.Print statement to the Worksheet_Change, Worksheet_Calculate, and ComboBox_Change event procedures. It seems that, when you make a change to either of the two worksheets, the first event that fires is the ComboBox_Change event.


    I'm not sure why that is the case, and I want to look to see if I can build a similar structure and replicate that.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Preventing Combo Box drop down list from activating when not in use


    Try this:

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Preventing Combo Box drop down list from activating when not in use


    Hi,


    Have a look at this test workbook. The ComboBox has Change event code to display a message box whenever it is triggered. There are two named ranges set up, both using formulas: rngData uses OFFSET, and rngIndexData uses INDEX. The data range itself is populated with static values, so the data within the table will never change.


    If you assign rngIndexData as the ListFillRange, and make a change to either worksheet, the event code is not triggered. If you change the ListFillRange to rngData, any change that you make to either worksheet will trigger the event.


    I'm not sure whether this suggests that there is a problem specifically within your workbook.

  • Re: Preventing Combo Box drop down list from activating when not in use


    G’day Guys



    Rory: This works perfectly Rory! It’s relieving, albeit a touch frustrating, that after all this time struggling with this it came down to modifying one little bit of code. Are you able to explain how/why this works? I’m quite a novice with VBA so I’m struggling to wrap my head around what is probably quite clear to your eye.



    Batman: Hey Batman, I couldn’t seem to enter the ‘rngData’ or ‘rngIndexData’ range names into the Fill List Range property of the Combo Box. The entry just keeps clearing when I hit enter. I’m assuming this might be because it is on another sheet.


    It certainly seems odd that it would work fine on another workbook but not on this one, if I run into trouble with Rory’s solution the only other approach I could take to my mind would be to reconstruct the spreadsheet from scratch which I may do anyway to try to pinpoint at which step it all falls apart.



    Cheers Guys



    Regards



    MattRNR

  • Re: Preventing Combo Box drop down list from activating when not in use


    G’day Rory


    I’m not sure if you remember this post, it has been some time, however you came up with a solution to a problem that had been plaguing me for months, concerning a google style dropdown search suggestions setup based on an article on trump excel.



    Your solution worked flawlessly on the original version (attached as EV1) of the spreadsheet I am working with however neither I nor a very knowledgeable bloke I’ve been working with from the Netherlands have been able to work out how to incorporate it into the full version of the spreadsheet (attached as EV3).



    I was hoping you might be able to show me how to replace the ‘Search Filter’ button on EV3's ‘Search Sheet’, with the google style dropdown search suggestions box your method made work in the original version (EV1)?



    I can’t for the life of me work out how to incorporate this method into the new version successfully.



    Though I am very novice to this stuff.



    Would really appreciate any help you could provide



    Cheers mate



    MattRNR

  • Re: Preventing Combo Box drop down list from activating when not in use


    Try this. I've used a different method that simply evaluates the H1 data in the database as you type, and added an Add button.

    Files

    • EV3.xlsm

      (223.65 kB, downloaded 111 times, last: )

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

  • Re: Preventing Combo Box drop down list from activating when not in use


    G’day Rory


    My apologies for taking so long to get back to you, I've been working out bush and haven't had reliable internet.


    This looks perfect; I can’t begin to tell you how much wasted time and hassle that drop down caused me before you gave me this solution.



    Really appreciate you taking the time to help me out on this last little complication as well.



    Cheers for everything mate



    Regards


    MattRNR

  • Re: Preventing Combo Box drop down list from activating when not in use


    Try this:


    Works like a charm!

  • Hi there,

    I stumbled across this thread when I was searching Google for the exact same problem that the original poster was experiencing. I have tried to reproduce the solution proposed by Rory, but there is a range mentioned in the code Range("DropDownList") on line 13, which I don't have in my file, causing subscript out of range.

    I'm very amateur on vba, and I can't work out what to replace this with. I have tried adding DropDownList as a named range on my Client List, referring to column E, but that obviously isnt the answer. I'm really hoping that Rory, or some other smart person, can take a look and let me know how to adapt my sheet to make this solution work? Or to adapt the solution to make it work for my sheet?

    My attached sheet contains the original code which causes the combo box to keep popping up, as well as the solution proposed by Rory, which I have commented out. The only change I have made to Rory's code is to rename the sheet in line 13 as "Client List", because my sheet and the original poster's sheet have different names.

    I would really appreciate any help.

    Thank you.

  • rory

    Approved the thread.