Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need One combo box row source to change based on the value of anot
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need One combo box row source to change based on the value of anot
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 "Katie" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need One combo box row source to change based on the value of
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 "Barry" wrote: 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 "Katie" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to enter data of one cell based on greatest amount of anot | Excel Discussion (Misc queries) | |||
Getting Combo boxes to change options based on other Combo boxes. | New Users to Excel | |||
Chart changing based on change in data source (number of rows/colu | Charts and Charting in Excel | |||
Change Pie Chart segment color based on value in source data | Charts and Charting in Excel | |||
assigning values to one cell which will change the formula in anot | Excel Worksheet Functions |