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:
The proposed solution code is this:
- Private Sub ComboBox1_Change()
- If ComboBox1.ListIndex < 0 Then
- End If
- End Sub
- Sub GetList()
- Dim rDDL As Range
- Set rDDL = Sheets("Database").Range("DropDownList") 'In my workbook "Database" needs to be replaced with "Client List".
- If rDDL.Count > 1 Then
- ComboBox1.List = rDDL.Value
- ComboBox1.List = Array(rDDL.Value)
- End If
- End Sub
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.