Populate Combobox with Dynamic Range problem

[sharky12345]

I'm using this to populate a series of Comboboxes on a Userform from a dynamic named range;

Me.Controls("ComboStaff" & i).List = ws.Range("NewAvailableStaff").Value

It works fine until there is only 1 item left in the range and then I get an error,

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

Is there a way I can avoid this?

 

[dmt32]

Hi,
try it like this

Dim arr As Variant
arr = ws.Range("NewAvailableStaff").Value
Me.Controls("ComboStaff" & i).List = IIf(IsArray(arr), arr, Array(arr))

Dave

 

[sharky12345]

Thanks Dave - it's progress, but now I get the same error when there are no values left in the range - so how can I adjust for this?

For info, the Combobox does enable users to free type in it so if there are no values left in the Combobox they can still enter details.