Help needed about ComboBox Add Item

[Syndp]

Hi,
In the attached sheet, there are 4 ComboBox. 1st "Item" will be selected in ComboBox1, then corresponding "Type" will be added in ComboBox2 additem list. After choosing desired "Type" from dropdown, corresponding "Description" will be added in ComboBox3 additem. In the same way, after selecting desired "Description", corresponding "Manufacturer" will be added in ComboBox4 additem.

Now, I have faced two problems.
Prob 1:: After selecting "Type" in ComboBox2 dropdown, ComboBox3 is not populating with corresponding "Description" values.
Prob 2:: I want only Unique values to be added in each ComboBox dropdown.

Please help me with these problems.

 

[Marc L]

Hi,​

​

according to your attachment and Excel basics as a VBA beginner starter :​

Sub Change(B)
    With Me.Controls("Combobox" & B + 1)
        .RowSource = "":  .Value = ""
    With Me.Controls("Combobox" & B)
        If .Value = "" Then Exit Sub Else [CX2].Cells(1, B) = .Value
    End With
        [A1].CurrentRegion.AdvancedFilter xlFilterCopy, [CX1:CX2].Resize(, B), [DB1].Cells(1, B), True
       .RowSource = Range([DB2].Cells(1, B), [DB1].Cells(1, B).End(xlDown)).Address
        If .RowSource Like "*$2" Then .Value = Range(.RowSource)
    End With
End Sub

Private Sub ComboBox1_Change()
    Change 1
End Sub

Private Sub ComboBox2_Change()
    Change 2
End Sub

Private Sub ComboBox3_Change()
    Change 3
End Sub

Private Sub CommandButton1_Click()
    [J2:J5] = Application.Transpose(Array(ComboBox1, ComboBox2, ComboBox3, ComboBox4))
    CommandButton2_Click
End Sub

Private Sub CommandButton2_Click()
    [CX1].CurrentRegion.Clear
    Unload Me
End Sub

Private Sub UserForm_Activate()
    [J2:J5].ClearContents
    [CX1:DD1] = Application.Index([A1:D1], , [{1,2,3,1,2,3,4}])
    [A1].CurrentRegion.Columns(1).AdvancedFilter xlFilterCopy, Empty, [DA1], True
    ComboBox1.RowSource = "DA2:DA" & [DA1].End(xlDown).Row
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Syndp]

Thank you so much Marc L. I don't understand the code properly but the code is working like a magic.

 

[Marc L]

Nothing magic but just an easy beginner level logic when operating manually using Excel basics :​

advanced filter and worksheet functions INDEX & TRANSPOSE as explained in Excel help like VBA help …​

​

 

[Marc L]

An alternative without an UserForm :​

Create dynamic (cascading) dropdowns with Data Validation

Find out how to set up cascading dynamic dropdowns using Excel's Data Validation, and that that reset if users later change their selection.

chandoo.org