Creating MultiColumns ListBox from VBA array

[supern]

Hi,

I'm trying to create a listbox regarding to a VBA "virtual" array I have.

for example:

Dim MyArray(3, 3)

For i = 1 To 3
For k = 1 To 3
MyArray(i, k) = i & k
Next
Next

Now, I want to enter the data to a multicolumn listbox (3 X 3) but don't know how.

Help?

[Ecniv]

Simple way - works until you get lots of data...

set the list box to values, the column count to 3

then create a string using your loop to add each item to display per row together.

example string
1;2;3;1;2;3;1;2;3

Then set the rowsource to the string you make.

Possibly requery the list box too.

[supern]

Not Working.. what did I do wrong?

MyStr = ""
 
    For i = 1 To XmlLastColumn
        MyStr = MyStr & XMLcolumns(i) & ";"
    Next
    
    MyStr = Left(MyStr, Len(MyStr) - 1)
    
    frmXmlColumns.lstXmlCol.RowSource = MyStr

[Ecniv]

Dim MyArray(3, 3)

For i = 1 To 3
    For k = 1 To 3
        MyArray(i, k) = i & k
        strToDisp = strToDisp & iif(len(strToDisp)>0,";","") & i & k
    Next
Next

frmXmlColumns.lstXmlCol.RowSource = MyStr

Have you set the list box to values and set the column count to 3 (probably need to set the column widths to "1;1;1" as well) ?

[supern]

OK... got the prob.. how do I set the listBox to values?

[Ecniv]

in MS Access

Design view
Select the list box
Right click > Properties (if the window isn't open)
Change to Data tab
The rowsource and type are there. Change the type to values.
Change tab back to format (the first tab)
Change column count and column widths here
Save form
Close and see if the code runs

[supern]

I'm sorry, I should have say that befrore - I'm using Excel, not Access. No "Type" field overthere...

[Ecniv]

I had a feeling you were going to say that.

Ok.. I tried via a userform > list box On the properties window, change to categorised. scroll down to data - should be on there.

[supern]

Sorry. No "Type" fuels ynder Data Category...

Excel do have:
1. BoundColumn (1)
2. ColumnCount (3)
3. ColumnHeads (False)
4. ColumnWisths
5. ControlSource
6. ListStyle (0 - fmListStylePlain)
7. RowSource
8. Text
9. TextColumn (-1)
10. TopIndex (-1)

[Ecniv]

Try Rowsource for the string data. Forget the type. I was thinking in Access sorry

[supern]

It didn't work... It works only for "real" source as rows and columns in the Excel sheet..

I will manage without it...

Thanks!!!