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

 

[ashvitha]

Hi! how do we insert the initialize method in the VBA code. Is it same as the On click event that we are supposed to insert the VBA code?

 

[epardo87]

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

Hey there!
I'm trying to to exactly what you did but just can't get it to work, the second ComboBox doesn't show anything, what might be wrong?

Private Sub UserForm_Initialize()
    ItemComboBox.AddItem "XCTU0039"
    ItemComboBox.AddItem "XSOL3910"
End Sub


Private Sub QtyComboBox_Change()
    Application.EnableEvents = False
    QtyComboBox.Clear
    Application.EnableEvents = True


    Select Case ItemComboBox.Value
        Case "XSOL3910"
            QtyComboBox.AddItem "4"
            QtyComboBox.AddItem "8"
            QtyComboBox.AddItem "12"
            QtyComboBox.AddItem "16"
            QtyComboBox.AddItem "20"
        Case "XCTU0039"
            QtyComboBox.AddItem "1"
            QtyComboBox.AddItem "2"
            QtyComboBox.AddItem "3"
    End Select


End Sub

 

[epardo87]

Never mind! i just noticed that the ComboBox to change must be the first one (Item in my example), I was changing at the second one (Qty)
Best Regards!

 

[Zanjo]

Is it possible to make the Case use values from a specific table column?

Example:

Select Case ItemComboBox.Value
Case "X"
ListObjects("Table1").ListColumns(2).Range.Select

or

Select Case ItemComboBox.Value
Case "X"
ActiveSheet.Range("Table1[[#All],[Column1]]").Value/List or something

 

[New Port]

Hi Norie,

Thanks for your above coding description.

I have 3 comboboxes, In 1st combo box I have simply used the range for required value. In 2nd combobox I have used your above coding and it is giving me correct list of data.

Now, I need to get data in 3rd Combobox based on the value of 1st and 2nd combobox.


Please suggest.


Thanks in advance.

 

[aaroncrotty]

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

Hey,

I tried this code but I got a error message saying: Compile error: Ambiguous name detected: ~

This is my code below:

Private Sub UserForm_Initialize()
    ComboBox1.AddItem "Canterbury/Swale"
    ComboBox1.AddItem "Thanet/Dover"
    ComboBox1.AddItem "Maidstone/Malling"
    ComboBox1.AddItem "Ashford/Shepway"
    ComboBox1.AddItem "SWK"
    ComboBox1.AddItem "DGS"
End Sub
Private Sub ComboBox1_Change()
    Application.EnableEvents = False
    ComboBox2.Clear
    Application.EnableEvents = True
    
Select Case ComboBox1.Value
    Case "Maidstone/Malling"
        ComboBox2.AddItem "Edenbridge"
    Case "SWK"
        ComboBox2.AddItem "Tonbridge"
End Select
End Sub

 

[Elipum]

Hi!
Thank you all very much. It is almost 10 years now since this post, but it has helped me a lot.

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.