Autofilter by combobox selection

  • Thread starter Thread starter CBartman
  • Start date Start date
C

CBartman

Hi there,
I'm stuck...
Sheet 1 contains rows of customer information.
Sheet 2 contains rows of machine information (including customer
number)Userform combobox #1 selection will filter machines range by customer
acct number to show only that customer's machines(I got that working). Now I
want to popluate a second combobox with the list of that customers machines.

My Sheet 1 named range is "Customers"
My Sheet 2 named range is "Equipment"

Currently, without autofilter, combobox #2 shows ALL machines, for ALL
customers.
With combobox #1 autofiltering the records, I'm only getting the top
"consecutive" records (1 - 4) showing up in the second combobox, not ALL
machines for that customer.

How do I specify (respecify) the combobox data source once the records are
filtered?
 
Might try to do something along these lines (code in the Userform
module):

Private Sub ComboBox1_AfterUpdate()
Populate_2ndCmbBx
End Sub

Private Sub Populate_2ndCmbBx()
Dim c As Range
Dim CstMshnCol As Integer'Col number holding the Machine names to
put in cbx 2.

CstMshnCol = 2
Me.ComboBox2.Clear 'Clear existing list

For Each c In Worksheets(Sheet2).Columns(CstMshnCol).Cells
If c.Row > 1 Then ' Assumes that 1st row holds headers and you
don't want _
header in the dropdown.
If c.Value = vbNullString Then Exit For ' You might want
to have differnt _
exit strategy - this one assumes that want to
stop adding _
items when you encounter the first empty cell
If Not c.EntireRow.Hidden Then'Checks that the row isn't
filterred out.
Me.ComboBox2.AddItem c.Value
End If
End If
Next c

End Sub
 
Back
Top