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

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

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

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

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