Userform combo box options based on another combo box

Newport Count

Active Member
Joined
Oct 25, 2009
Messages
328
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
 
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?
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Here's one example...
Code:
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?

Code:
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
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Here's one example...
Code:
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:

Code:
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
 
Upvote 0
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.
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,204
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top