Combobox to list only related item

[kimberly090]

I have a userfrom with 2 combobox

I wish to have my combobox2 will only list down the related item based on the value that being selected at combobox1

my combobox1 will contain the supplier list inside the data sheet while combobox 2 will only list the product which is related to the supplier.

others than that, the combobox1 will only show the unique supplier name only.

Here will be my data sheet information:

 

[bsuperior]

Try this Code

Private Sub ComboBox1_Change()
Dim uniquelistCollection As New CollectionDim
 cellRange As Range
Dim keyString As String
ComboBox2.Clear
If ComboBox1.Text <> "" Then
    For Each cellRange In Sheet1.Range("B2:B7")
        If cellRange.Offset(0, -1).Text = ComboBox1.Text Then
            keyString = cellRange.Text & cellRange.Offset(0, -1).Text
            On Error Resume Next
            uniquelistCollection.Add cellRange, keyString
            If Err.Number = 0 Then ComboBox2.AddItem cellRange.Value
        End If
    Next cellRange
End If
End Sub

Private Sub UserForm_Initialize()
Dim uniquelistCollection As New Collection
Dim cellRange As Range
For Each cellRange In Sheet1.Range("A2:A7")
    On Error Resume Next
    uniquelistCollection.Add cellRange, cellRange.Text
    If Err.Number = 0 Then ComboBox1.AddItem cellRange.Value
Next cellRange
End Sub

Feel free to follow my blog for more tutorials, link