Populating Combo from Single Row Named Range

[mattygriff]

Hi,

I have set up a userform with a combobox that I am dynamically populating from a number of named ranges - the range used is defined by other selections made on the userform.

Everything works fine if the range being used has multiple rows but a couple of them are only single entry and whenever one of these is selected I get "Run-time error '381': Could not set List property. Invalid property array index."

I am using the following code to populate the combobox:

Private Sub PopulateCombo(nr)

    With Me.cboBoundaries
        .Clear
        .List = Application.Transpose(Range(nr))
    End With

End Sub

where nr is the name of the range to be used.

Thanks in advance.

 

[PHV]

What about this ?

Private Sub PopulateCombo(nr)
Dim r As Range
Set r = Range(nr)
With Me.cboBoundaries
  .Clear
  If r.Rows.Count > 1 Then
    .List = Application.Transpose(r)
  Else
    .AddItem r
  End If
End With
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mattygriff]

Perfect, PHV, that works a treat - thanks!

So, out of interest, do you know why my original code didn't work? Presumably .Transpose isn't compatible with one-row ranges?