PDA

View Full Version : Solved: ComboBox and Named Range



ericc
12-13-2007, 01:55 PM
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 :doh:(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
12-13-2007, 02:27 PM
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)

Dim objCell As Range

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


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.

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

Bob Phillips
12-13-2007, 03:48 PM
Dim ary As Variant

ary = Application.Transpose(Range("H1:L1"))
Me.ComboBox1.List = ary

ericc
12-14-2007, 02:02 AM
Excellent,

This is exactly what I am looking for !!


Thanks you guys

ericc