Need One combo box row source to change based on the value of anot

[Guest]

I can't seem to get it to work. I keep getting "object doesn't support this
property or method, even though I see the property in the list of options.

I have ProdCatValue defined as a public string variable, and I call the
procedure from the first dropdown box.


Here is the code:

Public Sub FilterProductNames()
Select Case ProdCatValue

Case "A"
Sheets("Form")!cboProductName.RowSource = "Reference!G4:G13"
Case "B"
Sheets("Form")!cboProductName.RowSource = "Reference!I4:I16"
Case "C"
Sheets("Form")!cboProductName.RowSource = "Reference!K4:K9"
Case "D"
Sheets("Form")!cboProductName.RowSource = "Reference!C19:c23"
Case "E"
Sheets("Form")!cboProductName.RowSource = "Reference!E19:E73"

Case Else

End Select

End Sub

I have also tried it with listfillrange instead of rowsource, but neither
works.

I have also tried it directly in the on change event for the first combo,
with everything enclosed. (I moved it to a public sub because I thought at
first that the data type for the listfillrange / rowsource property was range
instead of string, and the range was on another worksheet.)

What am I doing wrong?

Katie

 

[Guest]

Hi Katie

Try using this code
Sheets("Form").cboProductName.ListFillRange = "Reference!A1:A10"

I've never seen the bang notation (!) used to reference controls in Excel,
although I know it is used in Access. Doesn't seem to work.

Also, I think the controls react to which container their in and although
you can use row source when the combo box is on a User Form, it doesn't seem
to be available on spreadsheets.

HTH

Barry

 

[Guest]

Thanks! That was exactly the problem. I had tried the . (dot) a bit
earlier, but I think at that point I had another thing wrong with my code...
Now everything works exactly as intented.

I really appreciate what a knowledgeable group gives their time to answer
questions here!

It is so quick that I always get my answer within a few days, and the few
times I've known an answer, by the time I went to post it, someone else
already had!

Thanks again.

Katie