VBA Form - MultiColumn ListBox

[iancook1982]

Hi,
I am trying to populate a multicolumn listbox on a form from the same workbook as the form is in. Here's what I have.

'define ranges
Dim SourceData As Range
Dim FundCount As Range
Dim records As Integer
Worksheets("clientlist").Select
Range("S1").Select
Range(Selection, Selection.End(xlDown)).Select
Set SourceData = Selection
records = Selection.Count
Range("T1").Select
Range(Selection, Selection.End(xlDown)).Select
Set FundCount = Selection

Dim ListItems As Variant
Dim i As Integer
With Me.ClientList
.Clear
.ColumnCount = 2
ListItems = SourceData.Cells.Value
ListItems = Application.WorksheetFunction.Transpose(ListItems)
For i = 0 To records
.AddItem
.List(i, 0) = SourceData(i)
.List(i, 1) = FundCount(i)
Next i
End With

The first part sets the arrays, then the 2nd tries to fill the listbox. But it bugs out at:
.List(i, 0) = SourceData(i)

Can you help? What have I missed?

Thanks,
Ian.

 

[Richard Schollar]

You don't appear to be using ListItems anywhere? Anyway, do you get a subscript out of range? This would be caused by SourceData having 2 dimensions so:

.List(i, 0) = SourceData(i+1,1)

 

[iancook1982]

that's great. thanks.

I got it working with:

Dim List As Variant
Dim i As Integer
With Me.ClientList
.Clear
.ColumnCount = 2
List = SourceData.Cells.Value
List = Application.WorksheetFunction.Transpose(ListItems)
For i = 0 To records
.AddItem
.List(i, 0) = SourceData(i + 1, 1)
.List(i, 1) = FundCount(i + 1, 1)
Next i
End With


How do I populate column headers?

Thanks,
Ian.

 

[Richard Schollar]

I don't think you can unless you tie the listbox into a range using RowSource