Three Comboboxes with one functioning dropdown

Question

I am having a problem incorporating three searchable comboboxes with dropdown lists.

I've created three searchable comboboxes with dropdowns in the way similar to this, that I fount on youtube (video). In short it's a combobox that you can type in, which dynamicaly updates the ListFillRange to corespond to the typed in string. Thus you can search for a country and then select it on a dynamic dropdown.

I have three of these comboboxes to select countries. The comboboxes are on one sheet and the dynamic lists (called CountryOne, CountryTwo, CountryThree - created in name manager) are on a seperate sheet. I've uploaded the file on dropbox.

For the comboboxes the following simple code is used:

Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "CountryOne"
ComboBox1.DropDown
End Sub

Private Sub ComboBox3_Change()
ComboBox3.ListFillRange = "CountryTwo"
ComboBox3.DropDown
End Sub

Private Sub ComboBox2_Change()
ComboBox2.ListFillRange = "CountryThree"
ComboBox2.DropDown
End Sub

Every one of the comboboxes works fine, until I select a country in one of them. After one country is selected (say in combobox1) and I move to another combobox (combobox2), the dropdown list for that combobox stops working. If I type something in combobox2 the dropdown is displayed under combobox1 (combobox with the selected country) and shows only the selected country. It's hard to explain, so please see the file.

To help in spotting the problem I've created the following video. In it I first test all the three comboboxes, and they work fine. Then I select a country in one of them, and the other two stop working properly.

Any idea to solve the problem will be very helpful.

It works for me. The only error i can see is that in row 22 the formulas are wrong, they take wrong range. But except of this it works ... at least i can't find any problems. Good work! — Daniel Dušek
@dee: Thank you for spotting the error in row 22. Bu otherwise the dropdowns don't work well at my side. Could the problem be in different Excel versions? I am using Excel 2013. — Gašper Zupančič
Hmm I do not know if the problem is Excel version. I have tested it with Excel 2007. But maybe I didn't follow the scenario which leads to the errors? To be sure you could create video and I'll try to reproduce exactly acording that video. — Daniel Dušek
@dee: I've filmed the problematic behaviour in the following movie: link. At first I test all the comboboxes and they work fine. Then I select a country in the first combobox (United Kindom under 'Country one') and then the other two stop working properly. — Gašper Zupančič
Hmm so I have followed the video but on my PC with Excel 2007 it behaves differently. I do not see any problems, all works fine. Obviously on Excel 2013 there it behaves differently I see now what you mean. Have you tryied to separate the three data sources which are now in the sheet 'lists' into three different sheets? So each combobox will have its own sheet with its own data. — Daniel Dušek

Gašper Zupančič Gašper Zupančič · Accepted Answer · 2014-12-09T15:43:19

I don't like to answer my own questions, but in case someone else finds this helpful.

I have somehow solved the issue, although I don't understand why the problem occurred in the first place. As I thought the problem was in DropDown lists automatically appearing in non active comboboxes. To avoid that i've made the following code change, with which the problem is not solved but by-passed:

Instead of

Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "CountryOne"
ComboBox1.DropDown
End Sub

I now use

Private Sub ComboBox1_Change()
ComboBox1.ListFillRange = "CountryOne"
If ComboBox1.ListCount <> 1 Then
    ComboBox1.DropDown
End If
End Sub

The added if clause disables dropdown list appearing automatically, when the length of the list is one - which means, when a country has been selected. Here one can find the 'repaired' file.

And thanks Dee for contributions.

Three Comboboxes with one functioning dropdown

1 Answers