excel 2010 VBA filling combo box based on selection from another

[DougP]

I have two combo boxes on a form. the first is looking at the column B to get its values. when I pick a value the first combo boxes [cboCostTracker] "change" event filters the data in both columns showing just the values for it. This is working just fine. for example if I pick 1223 then it correctly filters so only NP12, NP13, NP14 are showing in Column A on the sheet. the second combo box [cboSPMID] is suppose to load only the 3 items for 1223 so a user can selct which one to use from the second combo box [cboSPMID]. or if I pick 1555 from the first combo box [cboCostTracker]the second [cboSPMID] should only load NP14 and NP19. of course this is a tiny example I have several thousand values in each column. It seems the second combo box is loading all values though, NP12 to PP98 and not filtering anything. can someone help me figure out why?
sheet1
A B
NP12 1223
NP13 1223
NP14 1223
NP19 1555
NP28 1555
OP12 2222
OP56 2222
PP98 2222
----------

Private Sub cboCostTracker_Change()
    Dim FilterValue As String
    FilterValue = Me.cboCostTracker
    With Sheets("DPSR")
        .Range("B2").AutoFilter Field:=2, Criteria1:=FilterValue
    End With
    
    'fill cost tracker codes
    With Sheets("DPSR")
        Set Rng = .Range("A2", .Range("A2").End(xlDown))
    End With
    
    For Each cell In Rng.Cells
        Me.cboSPMID.AddItem cell.Text
    Next cell
End Sub

TIA

DougP

 

[PHV]

Me.cboSPMID.AddItem cell.Text
Where is cboSPMID cleared before that ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Andrzejek]

Are you sure when you pick the value from cboCostTracker the "change" event fires? Do you type or delete anything from the text in the combo? That's when change event takes place.

Try to use "Click" event instead and see if that will make any difference.

You can make sure you even go thru your code by placing the breake in you code and step thru it.

Have fun.

---- Andy

 

[SkipVought]

hi,

I do this sort of thing regularly.

Use the selection from the first combobox to be the criteria for a QUERY to return the list that you want. I'd do this on another sheet, via Data > Get Externmal Data > From other sources > MS Query... and drill down to your workbook. This assumes that the table that you posted is on a separate sheet with unique headings in row 1 -- in other words, a PROPER TABLE.

Then use that as the rowsource for the second combobox.

faq68-5829
faq68-5184

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}