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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Aaron

What have you tried so far?

Where is the data coming from and how have you populated the array?
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700
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
;)
 

Airn5475

New Member
Joined
Jan 29, 2007
Messages
38
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).
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700

ADVERTISEMENT

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. ;)
 

Airn5475

New Member
Joined
Jan 29, 2007
Messages
38
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.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Can't you use the RowSource property?

If you want 'proper' headers that's the only way your going to get them.
 

Airn5475

New Member
Joined
Jan 29, 2007
Messages
38
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?
 

Airn5475

New Member
Joined
Jan 29, 2007
Messages
38

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,622
Members
414,082
Latest member
sasmita

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
Top