Preventing ComboBox from activating when not in use

  • Hi there,

    I am having a problem with a Combo Box that I have created with a "Find as you type" kind of functionality, which allows you to type in the combo box for any part of a list of results, and the drop list only shows options which contain the typed values.

    The method relies on a bunch of formulas and a small amount of vba code triggered by the combo box change event, and it is a method I found online at this link: Trumpexcel's_searchable_drop_list

    The problem I am experiencing is that when the combo box is not in use, and even when the sheet that it appears on does not have focus, the combo box gains focus and the drop list appears over whatever the user is doing.

    This same problem has been discussed on another thread, with this link: Previous_Thread

    Based on the previous thread, it appears that the problem is caused by the fact that when any value in the workbook changes, workbook calculations are triggered, which in turn triggers the combo box change event (although I may be misunderstanding the problem).

    A solution was provided on the other thread, and I have tried to replicate that solution, but unfortunately the solution needed some adaptions to match my sheet, and I cant quite work out what I need to change.


    The original code for the combo box change event is this:

    Code
    1. Private Sub ComboBox1_Change()
    2. ComboBox1.ListFillRange = "DropDownList"
    3. Me.ComboBox1.DropDown
    4. End Sub

    The proposed solution code is this:


    I'm fairly certain that the problem is that I dont have a Range("DropDownList") in my sheet. I have tried to work through the files in the original post and figure out what I need to do to make this solution work, but I didn't manage. Any help would be really appreciated. I have attached my workbook.

  • Hello,


    If my understanding of your problem is correct ...

    In your sheet 1 module, you could add the following


    Code
    1. Private Sub Worksheet_Activate()
    2. Range("A11").Select
    3. End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • My apologies,

    I have sent some incorrect information in my original post. (My actual file had a lot more content, as well as some code I had used for failed workarounds, and I tried to strip all of that out and revert back to the original code suggested by trumpexcel so that I could provide the question without any clutter or confusion, but I may have created more confusion :(). I just went to test the solution suggested by Carim (Thank you Carim) and I noticed that the file doesnt work at all, because of that error.


    1) My original vba code actually reads:


    Code
    1. Private Sub ComboBox1_Change()
    2. ComboBox1.ListFillRange = "ClientList" ' in my first post this says "DropDownList"
    3. Me.ComboBox1.DropDown
    4. End Sub


    The solution proposed in the linked thread would be adapted as follows:


    And the error that I get when I try to run that code is "Run-time error '9' - subscript out of range" with the highlighted row being

    Code
    1. Set rDDL = Sheets("Client List").Range("ClientList")


    I am attaching a new copy of the file here. Sorry for the confusion.

  • Hi Carim, thank you so much. Your method is so much neater and than mine. The only problem is that it no longer searches for text that is contained within the client list. In other words, if I type in "Wealth" then the results jump to Wellman, but dont return any results, because the result I am looking for contains "Wealth" but does not start with it. I need to find all results that contain Wealth in any part of the string.

  • Hi,


    Attached is your Version 2 intuitive search with a Data Validation ComboBox ...


    where you do need to type in a few letters (3 to 5) in order to get the filter working ...


    Hope this will help

    Files

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello,


    Attached is your test file with a Data Validation ComboBox and its dynamic search feature ...


    Hope this will help

    Hi Carim,


    I've been attempting non-stop for the past few days to recreate your solution for my worksheet but to no avail. I was wondering if you could provide any guidance on how to adapt your code to a different worksheet? I'm a complete newbie at using VBA so I'm having a difficult time with this. I've also attached a stripped down version of my worksheet for reference although it should have all the information you need.


    Essentially, I've been trying to create a dynamic searchable drop down list function. However, the combo box keeps getting activated when I do other activities in other parts of excel (even in different workbooks). Would appreciate any and all help.


    Thank you!

  • ahleesohn


    Welcome to the Forum. Please read the Forum Rules then start your own thread. It is not allowed to post a question in another person's post.


    Also, if you search my recent posts you will find an easy example of adding a ComboBox