Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter by combobox selection
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilter by combobox selection
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 On Oct 13, 6:55*pm, CBartman wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
Combobox items determined by the selection in another combobox | Excel Programming | |||
Make Combobox act like autofilter | Excel Programming | |||
VBA question - autofilter combobox | Excel Programming | |||
ComboBox populated with data from AutoFilter | Excel Programming |