Autofilter by combobox selection

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?
 
A

AB

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top