Dependent comboboxes

Hello Excelforum.

So I was wondering how I can make it so that if I choose one option in the first combo box, the second combobox's options will be narrowed down based on the first, and so on. Strongly prefer if they were kept at comboboxes and not changed to validation drop-down lists because of the invisible drop down arrows.

For example, in this worksheet, if I pick "California" in the State combobox, then I would like the choices in the City combobox to narrow down to Los Angeles and Compton. After that, if I pick "Los Angeles" in the City combobox, then the Company Name combobox will only have the options "Google" and "Aksys".

Additionally, since there are duplicate entries for the State and City fields, is there a way to have the combo box selections not show duplicates?

Trucking Consolidation.xlsm

Are you against going with Data validation versus the comboboxes?

Benefit of data validation, no code required.

EDIT: Data validation example

Hi,

This should get you started although the data validation would be just as easy. The ListFillRanges are the cities you want. The code goes into ComboBox1.

Private Sub ComboBox1_Change()
If ComboBox1.Value = "California" Then
ComboBox2.ListFillRange = "N10:N11"
End If
If ComboBox1.Value = "Massachusetts" Then
ComboBox2.ListFillRange = "O10:O11"
End If
If ComboBox1.Value = "Rhode Island" Then
ComboBox2.ListFillRange = "P10:P11"
End If
End Sub

You could also use SELECT/CASE arguments.

HTH
Steve

jefferybrown:

Yeah, I strongly prefer comboboxes because my department is most certainly not computer savvy, to the extent where I'd rather not even have anything remotely hidden (such as the data validation arrows). The worksheet I attached will be part of a shared database so I'm making it as user-friendly as possible.


SteveG:

Let me try those, since what's also important is that it still works after I add more company information to the "Data" sheet.

Edit: I looked over the code one more time and realized that having to manually name all the entries wouldn't fly too well with the constant adding of information and as well as the whole user-friendly theme. I guess I was looking for something more automated. For example, if I were to enter another company under the state of California or a new state altogether, then the comboboxes return the data accordingly.


Thanks guys for the help so far!

I understand what you are saying about the drop down arrows not being visible, but maybe you place a light yellow fill on the cell and inform user, "Use the light yellow filled cells".

As far as updating the values, you can make those ranges dynamic so if you entered Utah underneath California, the named range woould automatically be updated.

In regards to a pivot table, it could work, but in the end, you still have to instruct user how to interact with it.

Let us know what else we can help with