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

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