Help with Dependent Combo Boxes

Hello,
I have had some help on here before with this project but I am running into a few issues that I don't know how to get around. I have 3 ComboBoxes that I want to have work off each other. I can one that chooses which division in our company it is. this one is labelled "company" and has 3 options. Then, depending on what you choose for the company will dictate which list of customers you see. Then, depending on which customer you choose, a list of contacts will be available for that specifc customer.

My two issues, I got help making the customers combobox dependent on the Company combobox but the drop down for the customers doesn't adjust in length based off of how many customers. the "Acoustics" customer list is only like 38 customers long and originally when I switched to Industry, I could only see the first 38 customers and the list stopped part way through. Now I can go into the code and see that it only looks from B1:B39 so if I change it to B1:B500 it will get everything but it adds the empty cells as well. So how do I get it to show only the filled boxes and that the number of filled boxes can be variable in each column.


My second issue is getting the Contacts Combobox tied into the mix as well. I don't have have the contacts list and I think I am uncomfortable adding them on here since it is real people. But basically if the company 3M comes up there might be 3 or 4 contacts that we might work with so 3M is in the Customer box I would like to see Jim Smith, Erik Johnson, Emily Anderson in the drop down for for Contact and then if I switch it to Stadler the list would have a whole new set of people. Also, We have 3M as a customer under Medical and under Industry and they have different contact names for each. Same company, different divisions. So Industry -> 3M would have a different set of contacts than Medical -> 3M.

Let me know if that makes sense. I would love for some insight here. I don't know how I should lay out a database for the contact info to make this work and I am struggling with the code to connect it to the customer.

try this approach all in one list, using a primary and secondary search key
torachan.

This one tweaked to include a combobox search on 'contacts'.

Checking it out now. For some reason I couldn't access this post for a while

So the comboboxes work the way I want but I am struggling to understand what you did in the code. I would like to figure out what is going on and make it work in my spreadsheet as it contains a lot of features that I am trying to include.

So I played with your code a bit and I got the combo boxes to work mostly how I need but I have a few issues that I was hoping you might be able to help me out with. First, in the code for the OK Button, I cannot get it to write the correct value for ComboBox3

Workbooks("Excel_quote_system_US_v6.xlsm").Worksheets("Offer Sheet").Range("B7") = Me.ComboBox3

This will have the Contact's name for Combobox3 but when it fills in "B7", it fills it in with the information in ComboBox1. It puts Acoustic. Same for this code

ws.Cells(newRow, 7).Value = Me.ComboBox3.Value

I cannot figure out why. I am trying to decipher what you wrote but I am not good at coding and there are aspects in there that I do not understand.

Also, For Combobox2 and Combobox3, If I type "a" it automatically selects the first option and moves on to the next box but if I have 2 options that start with the letter "a", I cannot get the opportunity to keep typing out my input to narrow my choice. It's just grabs the first option and moves on. I was wondering if you could help me understand what is going on here.

Thanks

Alter the ComboBox rows to .Text instead of .Value

ws.Cells(newRow, 7).Value = Me.ComboBox3.Text

Also I would advise altering your sheet selection line from Active to actual sheet

Set ws = Sheet1

The comboboxes are intended to select from the filtered dropdowns not intended for typein - defeats the purpose of pre-filtering.