Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically assign ComboBox.List from named range areas
Hi guys
I would like to assign a list of values for a ComboBox. I have a named range of various columns and based on the selection in another ComboBox, I would like to select the relevant Area of the named range. The code looks like this: iBrand = cboBrands.ListIndex + 1 cboProducts.List = Range("AllBrands").Areas(iBrand).Cells.Value The error message I receive is: Run-time error '381': Could not set the List property. Invalid property array index. This seems very strange as I feel I have thoroughly debugged. I have ascertained the validity of Range("AllBrands"), iBrand, etc. Any suggestions appreciated. Paul Martin Melbourne, Australia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically assign ComboBox.List from named range areas
The .List property is for referencing a specific element in the list, and the
syntax is object.List( row, column ) [= Variant] row Required. An integer with a range from 0 to one less than the number of entries in the list. column Required. An integer with a range from 0 to one less than the number of columns. Variant Optional. The contents of the specified entry in the ListBox or ComboBox. The error message is because you are not specifying row and column. If you want to use the .List property to create the Combobox's list of choices you will need to step through the rows (and columns? if you need them) to fill the list; or alternatively use the .AddItem method (easier for a one column list): Dim ListFillRange as Range, ListCell as Range Set ListFillRange = Range("AllBrands").Areas(iBrand) cboProducts.Clear For each ListCell in ListFillRange.Cells cboProducts.AddItem ListCell.Value Next ListCell -- - K Dales "Paul Martin" wrote: Hi guys I would like to assign a list of values for a ComboBox. I have a named range of various columns and based on the selection in another ComboBox, I would like to select the relevant Area of the named range. The code looks like this: iBrand = cboBrands.ListIndex + 1 cboProducts.List = Range("AllBrands").Areas(iBrand).Cells.Value The error message I receive is: Run-time error '381': Could not set the List property. Invalid property array index. This seems very strange as I feel I have thoroughly debugged. I have ascertained the validity of Range("AllBrands"), iBrand, etc. Any suggestions appreciated. Paul Martin Melbourne, Australia |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically assign ComboBox.List from named range areas
Thanks for the reply, but List can be assigned as documented on MS KB
<http://support.microsoft.com/default.aspx?scid=kb;en-us;161598 ListBox1.List = Array("One", "Two", "Three", "Four") Regards Paul Martin Melbourne, Australia |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically assign ComboBox.List from named range areas
My apologies, you are quite right.
The only thing I can think that might cause the problem is if you have some other property of the combobox set that keeps you from updating the list. For example, if I set the RowSource property and then try to programatically set the .List to a range of values I get the error message "Permission Denied" (Win XP, Excel 2002). The only other suggestion, if you have not done so already,is to be sure cboProducts is assigned as a Combobox explicitly, not a generic Dim cboProducts as object. Sometimes that can make a difference. -- - K Dales "Paul Martin" wrote: Thanks for the reply, but List can be assigned as documented on MS KB <http://support.microsoft.com/default.aspx?scid=kb;en-us;161598 ListBox1.List = Array("One", "Two", "Three", "Four") Regards Paul Martin Melbourne, Australia |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Dynamically assign ComboBox.List from named range areas
I don't have any other properties set that conflict (already checked
that). cboProducts is a ComboBox object painted on a UserForm so no problem with declaration either. Thanks anyway. Regards Paul Martin Melbourne, Australia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamically Named Range and IsError() | Excel Worksheet Functions | |||
Add a data series dynamically to a named range? | Charts and Charting in Excel | |||
How to dynamically reference a dynamic named range | Excel Worksheet Functions | |||
Dynamically assign items to a list box | Excel Programming | |||
Dynamically assign items to a list box | Excel Programming |