View Single Post
  #10   Report Post  
Posted to microsoft.public.excel.programming
GS[_2_] GS[_2_] is offline
external usenet poster
 
Posts: 3,514
Default Convert list to UPPER, lower & Proper cases.

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