+ Reply to Thread
Results 1 to 5 of 5

Dynamically assign ComboBox.List from named range areas

  1. #1
    Paul Martin
    Guest

    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. #2
    K Dales
    Guest

    RE: 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. #3
    Paul Martin
    Guest

    Re: 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. #4
    K Dales
    Guest

    Re: 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. #5
    Paul Martin
    Guest

    Re: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1