Not surprisingly, you can't 'dump' a range directly into a list
control because the indexing is invalid due to a range array being
1-based-2D. In this case you need to load the list into another array
(or populate each list item individually)...
With Application
Me.ComboBox1.List = .Transpose(.Index(Range("MyList"), 0, 1))
End With
..where "MyList" is a named range containing the items to display in
the ComboBox1.List.
I guess it would be prudent for me to explain how the above code works!
Just as we can ref a range array via the INDEX() function, so too can
we ref a row or col of a 2D array. In the case of a list control, we
need to transpose the elements to a vertical list since, by default,
Index() results a horizontal list...
Doing a row of a 2D array:
With Application
Me.ComboBox1.List = .Transpose(.Index(Range("MyList"), 1, 0))
End With
...where "MyList" is a horizontal named range this time.
This is not necessary, though, when populating a multi-column list from
a 2D array...
Dim vData
vData = ActiveSheet.UsedRange
With ListBox1
.ColumnCount = UBound(vData, 2) '//# of cols in the array
.List = vData
End With
...where ListCount will be UBound(vData), but the first item index for
both rows/cols is zero.
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic
VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion