Dear All and MVPs, I have created my userform and there are some dropdown box
there. In order to ease and simply user on selecting, this userform works
similarly and base on Autofilter from the workbook. E.g., a file contains
Company Name and Product Name. Since some companies may carry more than one
product, after user selected the company on my userform, the product
Combo(dropdown) listbox only shows the product(s) the company carries. My
method is using Autofilter on the worksheet and set the criteria as the user
selection on the workbook. How can I get the autofilter list from the Excel
autofiler? Or I have to do looping to check? Please advise. Thanks in advance.

I'd just loop through the range...

Option Explicit
Private Sub ComboBox1_Change()

Dim myRng As Range
Dim myCell As Range

Me.ComboBox2.Clear

With Worksheets("sheet1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then
Me.ComboBox2.AddItem myCell.Offset(0, 1).Value
End If
Next myCell

End Sub

I matched up on column A and took the value from column B.

hanawakun wrote:

Dear All and MVPs, I have created my userform and there are some dropdown box
there. In order to ease and simply user on selecting, this userform works
similarly and base on Autofilter from the workbook. E.g., a file contains
Company Name and Product Name. Since some companies may carry more than one
product, after user selected the company on my userform, the product
Combo(dropdown) listbox only shows the product(s) the company carries. My
method is using Autofilter on the worksheet and set the criteria as the user
selection on the workbook. How can I get the autofilter list from the Excel
autofiler? Or I have to do looping to check? Please advise. Thanks in advance.

--

Dave Peterson