Populating multicolumns in listbox in Excel 1

[timbo44]

Hi,

I need to populate the columns of a multicolumn listbox on a userform in Excel. I have read data from a text file and have ended up with an array of the values I wish to write to the listbox. A simple example:-

array = "one","two","three","four"

I do not know in advance how many rows there are in the text file, although I do know how many fields(columns), so any solution would allow me to iterate around until the end of the text file is reached. After I've read the line of data, and split this into separate fields, I want to write each field into the coresponding column in the listbox. Sounds easy but I'm having problems. I have tried:-

MyFile = IMPORTFILE(fImport)
With frmmain
.lstRunway.ColumnCount = 4
End With
For intI = 0 To UBound(MyFile)

S = MyFile(intI)
rtnTXT = SSPLIT(S)

With frmmain
.lstRunway.Column(0, intI) = rtnTXT(0)
.lstRunway.Column(1, intI) = rtnTXT(1)
.lstRunway.Column(2, intI) = rtnTXT(2)
.lstRunway.Column(3, intI) = rtnTXT(3)
End With

Next intI

IMPORTFILE is a custom function which returns a variant (string array).

SSPLIT is a custom function that splits a line into separate fields (uses SPLIT function but handles tabs and multiple spaces as well)

rtnTXT is a variant

I keep getting the error:-

Could not set the Column property. Invalid property array index.

Any pointers greatly appretiated

Tim

 

[combo]

It is possible to assign 2D variant array directly to the listbox (List property, matching of columns number required): ListBox1.List() = VArray.

combo

 

[timbo44]

I cant do that as the array will be N rows by 4 columns. I dont know how many rows I need and you can't seem to redimension a dynamic array (possibly a new post?). If I use a custom type, and build an array of this type holding the values which can be redimensioned, I can not use this array with the listbox as I get the warning about user types can't coerce etc.

 

[combo]

I do not know the structure of your data, but using column (transposes input array) it is possible to resize input data (two column list):

Dim a() As Variant

Private Sub CommandButton1_Click()
ReDim a(1 To 2, 1 To 2)
a(1, 1) = 1
a(2, 1) = 4
a(2, 2) = 5
a(1, 2) = 2
ReDim Preserve a(1 To 2, 1 To 3)
a(1, 3) = 3
a(2, 3) = 6
Me.ListBox1.Column() = a
End Sub

combo

 

[timbo44]

Thanks

I'll give it a go and let you know

Thanks