Userform combo box options based on another combo box

[Newport Count]

Hi is it possible to have two drop down boxes in a userform that are linked?

I need the option chosen in combo box A to determine the options that are then available to choose from in combo box B

Many thanks

 

[AlphaFrog]

Here's one example...

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Grains"
    ComboBox1.AddItem "Fruits"
    ComboBox1.AddItem "Dairy"
End Sub

Private Sub ComboBox1_Change()
    Application.EnableEvents = False
    ComboBox2.Clear
    Application.EnableEvents = True
    
    Select Case ComboBox1.Value
        Case "Grains"
            ComboBox2.AddItem "Cereals"
            ComboBox2.AddItem "Breads"
            ComboBox2.AddItem "Pastsa"
        Case "Fruits"
            ComboBox2.AddItem "Apples"
            ComboBox2.AddItem "Oranges"
            ComboBox2.AddItem "Pears"
        Case "Dairy"
            ComboBox2.AddItem "Cheese"
            ComboBox2.AddItem "Milk"
            ComboBox2.AddItem "Yogurt"
    End Select
End Sub

 

[Newport Count]

thanks for that - it does work but i would require much longer lists

is it possible to use similar code to look up information from another tab or separate spreadsheet?

for example combo box 1 will display a company name, combo box 2 will only display the contact names relevant for that selected company

it might be a lot of data to enter into the code as .AddItem etc - it would be a great help if it could lookup information from elsewhere?


many thanks!

 

[Norie]

You can use lists/ranges on worksheets for this sort of thing.

How to do it really depends on how you've arranged them?

Taking your example, will you have a list of unique comapanies?

What about the contacts? Where/how would they be listed/stored...

If you don't have any sort of list you would need to set something up.

 

[Newport Count]

OK so if I have a list set up in a different worksheet (e.g. listdata) then it would looks as follows:

column A - Company name
column B - Contact Name
column C - Telephone number
column D - Email address

(there could be several contact names sharing the same company name)

how would this work in the code provided?

thanks in advance

 

[Norie]

Do you also have a unique list of the companies?

If you do then you should populate the first combobox using that.

Then when a company is selected you can simply go down the contact list checking the company name.

If it matches the selected company add it to the contacts combobox.

Private Sub cboCompanyName_Change()
Dim rngCompany As Range
Dim rngList As Range
Dim strSelected As String
Dim LastRow As Long
   
        ' check that a company has been selected 
      If cboCompanyName.ListIndex <>-1 Then
 
           strSelected = cboCompanyName.Value
          
           LastRow =  Worksheets("ListData").Range("A" & Rows.Count).End(xlUp).Row
 
           Set rngList = Worksheets("ListData").Range("A2:A" & LastRow)
 
           For Each rngCompany In rngList
 
                 If rngCompany.Value = strSelected Then
 
                      cboContacts.AddItem rngCompany.Offset(,1)
 
                 End If
 
           Next rngContent
 
      End If
 
End Sub

Hope that makes sense - I can post a link to a sample file.

 

[Newport Count]

Hi,

thanks for that - i couldnt work out why it didnt work for a while - then i noticed that the last line should have been Next rngCompany instead of Next rngContent!

it does work but i notice that when i reset my form - the options that have previosuly been displayed in Contact Name are available to choose from in the drop-down box before the Company Name has been chosen?

The same happens if you select a company name and then decide to choose a different one without selecting anything in the Contact Name drop down.......both company contact names appear in the Contact Name drop down when one company is eventually selected

any ideas how to fix this?


Thanks in advance

 

[Norie]

The code I posted was just sort of off the top of my head, so it would definitely need to be adapted to take into account your setup.

Halfway through I realised I had an existing file which basically did the same, but the company/contact were the other way round.

I'm not sure how you 'reset' the formula so I don't know how that can be fixed.

Can you post the code you are using for that?

The other thing is easily sorted - just add this line after the If

cboContacts.Clear

That will clear the combobox of the contacts from the previously selected company.

 

[Newport Count]

can you clarify where i should add:

The other thing is easily sorted - just add this line after the If

Code:

cboContacts.Clear</PRE>
That will clear the combobox of the contacts from the previously selected company.

thanks again!

 

[Newport Count]

Hi - i have played about with it and got it to work!

many thanks for your help - i think i have exactly what i need

much appreciated