Array to Userform Listbox

Airn5475

New Member
Joined
Jan 29, 2007
Messages
38
Hello,
I have been searching the internet and this sight and have found many variations, but I cannot seem to get my listbox on a Userform to populate.
The array is 10 columns by 5 rows. I can get it to add one long column of data, but this is obviously now what I want.

Can anyone help or point me in the right direction?
Thank you in advance,
Aaron
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Aaron

What have you tried so far?

Where is the data coming from and how have you populated the array?
 
Upvote 0
Hello,

Try setting the ColumnCount Property, then passing your array, e.g.,

Code:
Sub foo()
Dim varArr() As Variant
Let varArr = _
    [{1,2,3,4,5;6,7,8,9,10;11,12,13,14,15;16,17,18,19,20}]
Load UserForm1
With UserForm1.ListBox1
    .Clear
    .ColumnCount = 5
    .List = Application.Transpose(varArr)
    .ListIndex = 0
End With
UserForm1.Show
End Sub
;)
 
Upvote 0
Try setting the ColumnCount Property, then passing your array, e.g.,

Worked on the first time but my rows turned into columns, my data is going down the list instead of across, lol.

I am also looking only to certain columns (1,2 & 5).
 
Upvote 0
Hello again,

Try transposing your array, like I did. I.e.,

Code:
.List = Application.Transpose(varArr)
If you want to pass certain vectors of your Array, I think you need to create an Array you'd actually like you use. ;)
 
Upvote 0
Form Name: User Form
Listbox Name: lboxProcs
Array: ProcArr
Array Row Count: ProcRowCnt
Array Col Count: ProcColCnt

The array gets its data from a spreadsheet range and I would prefer to load the spreadsheet into the array and go from there instead of going directly from the spreadsheet.

What have you tried so far?
I did different variations of the following:

Code:
For Row = 1 To ProcRowCnt
        MainForm.lboxProcs.AddItem ProcArr(Row, 1).Value
        For Col = 2 To ProcColCnt
            MainForm.lboxProcs.List(Row, Col) = ProcArr(Row, Col).Value
    Next Col
Next Row

--and--
Code:
MainForm.lboxProcs.List = ProcArr

Also, is it possible to get ColumnHeaders? My array does not currently hold them.
 
Upvote 0
Can't you use the RowSource property?

If you want 'proper' headers that's the only way your going to get them.
 
Upvote 0
If you want to pass certain vectors of your Array, I think you need to create an Array you'd actually like you use.

Thanks Nate for your help thus far, I don't think I follow you though.
My data in my spreadsheet is as follows:

|Name|Description|Required|...etc
|Aaron|VBA |Yes |...etc
|Nate |VBA |No |...etc

Your code turned it into the following in my listbox:
|Aaron|Nate|..etc
|VBA |VBA|...etc

Does that make sense?
 
Upvote 0
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top