VBA Excel- populate combobox2 dependent on Combobox1

[Raymondc190466]

Dear All,

I want ComboBox2 to be populated depending on the value choosen in ComboBox1.

I have created 3 name ranges:
Vendor = Column A => =OFFSET(Supplier!$A$2;1;0;COUNTA(Supplier!$A:$A)-1)
VendorNumber = Column B => =OFFSET(Supplier!$B$2;1;0;COUNTA(Supplier!$B:$B)-1)
Material = Column C => =OFFSET(Supplier!$C$2;1;0;COUNTA(Supplier!$C:$C)-1)

Present code to populate ComboBox1:

Private Sub UserForm_Initialize()
Set xRg = Worksheets("Supplier").Range("A2:C127")
    Me.ComboBox1.List = xRg.Columns(1).Value

I have below code to populate the textbox7:

Private Sub ComboBox1_Change()

    'Select Supplier - ComboBox1, autofill Textbox7 with Suppliernumber

Dim xRg As Range
 
    With Worksheets("Supplier")
        Set xRg = .Range("A2:C" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With
 
    If IsError(Application.VLOOKUP(Me.ComboBox1.Value, xRg, 2, False)) Then
        MsgBox "Start new Complaint " & Me.ComboBox1.Value
    Else
        Me.TextBox7.Text = Application.VLOOKUP(Me.ComboBox1.Value, xRg, 2, False)
    End If

End Sub

What code do I need to get this in the ComboBox1_Change and to fill Combobox2?

 

[Raymondc190466]

Dear All,

I have found a solution (google is my friend):

Private Sub cboSupplier_Change()

    'Select Supplier, autofill Textbox with Suppliernumber

Dim xRg As Range
Dim r As Integer
Dim ws As Worksheet

Worksheets("Supplier").Activate
    
r = 2

Range("F2").Value = cboSupplier.Value

Columns("C:D").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "F1:F2"), CopyToRange:=Range("H1:I1"), Unique:=False

cboMaterial.Clear

Do Until Cells(r, 9).Value = ""
    cboMaterial.AddItem Cells(r, 9).Value
    r = r + 1
Loop

With Worksheets("Supplier")
        Set xRg = .Range("A2:B127" & .Range("A" & .Rows.Count).End(xlUp).Row)
    End With
  
    If IsError(Application.VLookup(Me.cboSupplier.Value, xRg, 2, False)) Then
        MsgBox "Start new Complaint " & Me.cboSupplier.Value
    Else
        Me.txtSupNr.Text = Application.VLookup(Me.cboSupplier.Value, xRg, 2, False)
    End If

End Sub