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:

    
    With Me.StaffNameCB2
        .Clear
        .List = Application.Transpose(Range("StaffName"))
     End With

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:

            With Me.TeamCB1
                .Clear
                .List = Application.Transpose(Range(WorksheetFunction.Substitute(Me.LineMgrCB1.Value, " ", "_")))
            End With

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:

        With Me.TeamCB1
                .Clear
                
                arrTeams = Application.Transpose(Range(WorksheetFunction.Substitute(Me.LineMgrCB1.Value, " ", "_")))

                If IsArray(arrTeams) Then
                    .List = arrTeams 'if multiple teams
                Else
                    .AddItem arrTeams 'if single team
                    .Value = arrTeams
                End If

            End With

Thanks again, TC