Solved: ComboBox and Named Range

[ericc]

Hello All

I have created a UserForm with a ComboBox for which the source is a named range.
Usually, it works nicelly but this time my range is horizontal (in a row) instead of vertical (in a column). And of course, I see only the first element of the list. In fact, if I put the parameter "ColumnCount" to 3 by example, I see 3 others values but horizontally (this is how I discover the problem).

I can solve this with a Copy then PasteSpecial/transpose on an other place of the sheet and then define the range from there. But I don't found this an "elegant" solution

Can you propose better ?

ericc

[Dr.K]

In my experience, RowSource works best for ComboBoxes embedded in worksheets. For ComboBoxes in UserForms, I usually use a loop to manually add items to the ComboBox.

Put this in your UserForm_Initialize() sub, and it won't matter WHAT shape or location your named range is.
(although, if the named range is in a different project, you'll need to specifiy the full path to the named range)

[vba]Dim objCell As Range

Me.ComboBox1.Clear
For Each objCell In Range("NamedRange")
Me.ComboBox1.AddItem objCell.Value
Next objCell[/vba]


Edit:
Oh yeah, if you are using a non-contiguous range that might contain blank cells, use an if statement qualified to filter out the blank cells.

[vba] Me.ComboBox1.Clear
For Each objCell In Range("NamedRange")
If Not objCell.Value = Empty Then
Me.ComboBox1.AddItem objCell.Value
End If
Next objCell[/vba]

[Bob Phillips]

[vba]

Dim ary As Variant

ary = Application.Transpose(Range("H1:L1"))
Me.ComboBox1.List = ary
[/vba]

[ericc]

Excellent,

This is exactly what I am looking for !!


Thanks you guys

ericc