RTE 381 Could not set the List property. Invalid property array index.

[jasonb75]

Hi all.

I've just tried populating a set of comboboxes in a form using the code below and I'm getting the message, Runtime Error 381 Could not set the List property. Invalid property array index.

A1:G1 contain the names of the comboboxes, the idea is to populate each of the boxes named in that range with a list found below it. This was just a quick attempt before packing up for the day so it's likely riddled with errors and bad ideas. I'm almost certain that Me.Controls(c.Value).List is wrong, I just don't seem to be able to find the correct way to express that part.

Private Sub UserForm_Initialize()
Dim rng As Range, c As Range, lRow As Long
With Lists
    Set rng = .Range("A1:G1")

    For Each c In rng
        lRow = .Cells(.Rows.Count, c.Column).End(xlUp).Row
        Me.Controls(c.Value).List = c.Offset(1).Resize(lRow - 1)
    Next c

End With
    Me.Show
End Sub

Any suggestions for a fix, or ways to improve would be appreciated.

Thanks in advance, Jason.

 

[John_w]

        Me.Controls(c.Value).List = c.Offset(1).Resize(lRow - 1).Value

The List property requires a Variant array, so you need .Value on the RHS to return a Variant array, otherwise it's a Range object.

 

[jasonb75]

Bingo!

Thank you very much, John. I had a feeling that I was missing something simple.