20 column in a ListBox ?

[Erdinç E. Karaçam]

Hi dear friends,

I need load 20 columns and 5 rows data to a ListBox from a Worksheet... (The content of data from an invoice...)

I have tried by AddItem and by RowSource methods but didn't worked and not useful...

Could you give me a sample VBA "array" code for this please ?

Thanks in advance...

Erdinç

 

[MickG]

Hi, Try this:-

[COLOR=navy]Sub[/COLOR] MG05Mar12
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range("A1:T5")
[COLOR=navy]With[/COLOR] ListBox1
.ColumnCount = 20
.ColumnWidths = "30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30"
.List = Rng.Value
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[Erdinç E. Karaçam]

Hi Mick,

Thanks for your kindly support.

But i need more than this...

I have a database which includes string, date, integer values... (About an invocie)

In this database, i need a for-next loop...

Such as;

For i = 2 To 1000
If Cells(i, 2).Value = "MyCriteria" Then
MyRange = MyRange & CurrentRowRange
End If
Next i

I need expand my range...

In your sample;
Set Rng = Range("A1:T5")

But;
If my control is true then my range should expand as;
Range("A2:T2")
+
Range("A3:T3")
+
Range("A6:T6")
+
Range("A16:T16")
etc...

Probably i need an array coding but i can not create yet ...

Many Thanks

 

[MickG]

Hi, Not sure how you want to actually ,expand you range, but the code below may give you some ideas, Based on your "Criteria".

[COLOR="Navy"]Sub[/COLOR] MG05Mar10
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, mycriteria, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
mycriteria = "col" '[COLOR="Green"][B] Your column "A" Criteria Here !!![/B][/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.count).End(xlUp))
ReDim Ray(1 To Rng.count, 1 To 20)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn = mycriteria [COLOR="Navy"]Then[/COLOR]
        c = c + 1
            [COLOR="Navy"]For[/COLOR] Ac = 1 To 20
                Ray(c, Ac) = Dn.Offset(, Ac - 1)
            [COLOR="Navy"]Next[/COLOR] Ac
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] ListBox1
.Clear
.ColumnCount = 20
.ColumnWidths = "30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30,30"
.List = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Erdinç E. Karaçam]

This is the excellent solution Mick!!!

Many many thanks...

 

[Norie]

Erdinç

Do you really want/need a 20 column listbox?

Will the user even be able to see all the data?

 

[Erdinç E. Karaçam]

Hi Norie,

Actually, yes!

I really need this...

I am trying to build a "Front Accountancy Program" and in this applicaiton; i want to see all the data in listbox... After then, i will sent all the datas to a worksheet from this listbox...

Just a little complicated but i have to do this anyway...

 

[Norie]

Well if you have to do it, you have to do it.

It just sounds like a bit of a design/visual nightmare to me, but that's me.

 

[Erdinç E. Karaçam]

On the other hand, i agree you, this isn't a beautiful visual scene....