+ Reply to Thread
Results 1 to 3 of 3

Populating ComboBox using dynamic named range - Error

  1. #1
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Populating ComboBox using dynamic named range - Error

    Hi,

    I have some code to populate userform comboboxes from named ranges within the same workbook, e.g:

    Please Login or Register  to view this content.
    This works perfectly.

    Now I wanted to set up some data dependant lists - i.e. selection in Combobox 1 (LineMgrCB1) defines the named range used to populate Combobox 2 (TeamCB1).
    The data is in the form of names so I've used substitute to replace spaces with underscores, so that I can use these to form the named ranges, e.g:

    Please Login or Register  to view this content.
    For example the named range for line manager Bob Smith is now Bob_Smith and withing his range are listed the teams he manages, TeamA, TeamB, etc. I hope this makes some logical sense.

    Again this works fine, providing the range is comprised of more than one cell, i.e. more than one team listed. However, where there is only one team under that line manager, I get the following error, and despite Googling have not managed to find a way round it:

    Run-time error '381':
    Could not set the List property. Invalid property array index.

    I'm trying to make this future proof for new teams and managers without the need to touch the code. Any ideas how to 'fix' this?

    Thanks, TC

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,626

    Re: Populating ComboBox using dynamic named range - Error

    Put the result of Application.Transpose... into a variable, then check if it's an array or not.

    If it's an array use List, if it's not use AddItem.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Populating ComboBox using dynamic named range - Error

    Hi Norie,

    That's great thanks, I've not come across IsArray before - but makes perfect sense.
    I've added in a bit of code to also populate the combobox if only one choice avaliable, and it's blank if there are multiple teams - just in case anyone else finds it useful, code below:

    Please Login or Register  to view this content.
    Thanks again, TC

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Error with Dynamic named range
    By kalisimone in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-03-2013, 02:52 PM
  2. activex combobox and dynamic named range
    By lcsw in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-22-2013, 05:30 PM
  3. getting an error when creating a dynamic named range
    By emesgee in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-21-2013, 10:37 PM
  4. Combobox ListFillRange using dynamic named range not updating
    By anteagles20 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-21-2013, 09:30 AM
  5. N/A Error on a dynamic named range
    By PelleCadol in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-14-2011, 08:19 AM

Tags for this Thread

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