Populating a Listbox from array

[Rocky0201]

Hi,

I have a listbox which I wish to have more than 10 columns (11 to be exact). I am trying to build an array with the items for each column but I am having problems understanding how to build the array, how to redim, etc. The array will have to have variable number of rows of 11 columns for the listbox.

Once I have the array built, I would set the listbox's column count to 11, set the column widths and then do a listbox6.list = varray

Please help...

Thanks,

 

[Domenic]

If you want to create an array from a range, and then assign it to your ListBox, maybe something like this...

    Dim vArray As Variant
    
    vArray = Range("A1", Cells(Rows.Count, "K").End(xlUp)).Value
    
    ListBox6.List = vArray

 

[jim may]

Domenic,
I created a Listbox1 directly on my Sheet1, and now am trying to complete the OP's request using your suggested code. My example as only 5 columns, and currently I'm at this point

Excel Workbook
	A	B	C	D	E	F
1
2		LName	FName	Address	City	State
3		Causey	Jack	Allendale	Statesville	NC
4		Cody	Bruce	SandleRidge	Roanoke	VA
5		Evans	Carl	Hideaway	Dallas	TX
6		May	Jim	Avenham	Roanoke	VA
7		Vaughn	Bud	Rt 460	Roanoke	VA
8		White	Jerry	Poplar Spr	Meridian	MS
Sheet1

Excel 2007

And current code used of:

Private Sub ListBox1_GotFocus()
Dim vArray As Variant
    
    vArray = ActiveSheet.Range("B3", Cells(Rows.Count, "F").End(xlUp)).Value
    'ListBox1.ColumnHeads = True    'thought this might help but it didn't
    ListBox1.List = vArray
End Sub

one of many problems currently is that the Listbox1 is showing a HeaderColumn (I manually set the properties box to True), but the contents are BLANK; Confused here..
can you help me?

 

[Domenic]

Jim,

To show the header row by setting the 'ColumnHeads' property to True, you would need to populate the ListBox by using the 'ListFillRange' property. I think if you want to show the header row when assigning an array to the List property, you would need to include that row in your array.

 

[jim may]

Thanks Domenic -- I've been able to move forward!! Jim

 

[Domenic]

You're very welcome, Jim!

 

[Rocky0201]

Domenic, Thanks for respopnding.

Here's my code. I am getting a Run-Time Error 381 - Could not set list property. Invalid property array index.

                Vf = ComboBox8.Value & "" & "" & Sheets("Resource Definition").Cells(i, 4) & _
                    Sheets("Resource Definition").Cells(i, 5) & _
                    Format(Sheets("Resource Definition").Cells(i, 6), "00000") & _
                    "" & "" & "" & "" & ""
                With ListBox6
                    .ColumnCount = 11
                    .ColumnWidths = "30;40;40;100;30;35;55;60;55;20;20"
                    .List = Vf
                End With

I certainly appreciate the help.

 

[Rocky0201]

Domenic,

Looking at my response in my previous post, I feel the need to explain further.

I am looping through listbox5 and pulling out the dat in the 8 columns. I am using this data to build 8 columns in listbox6 however, I am adding another 3 columns to listbox 6 to insert other data.

Since additem can only support 10 columns, I wish to build an array with all 11 columns and all rows (listcount from listbox 5) and populate listbox6.

My confusion is how to construct an array so that I can complete the task mentioned above.

Hope this makes sense...

Thanks again Domenic.

 

[Norie]

Where does the data come from for the 3 columns that don't come from Listbox8?

Also, do you want to populate ListBox6 with all the data from Listbox8?

PS Are the listboxes on a userform or worksheet?

 

[Rocky0201]

Thanks for responding Norie.

The listboxes are on userform1.

I am using all 8 columns from listbox5.

The 11 columns in listbox6 will consist of:

Column 0 = Value of ComboBox8
Columns 1 thru 8 = Columns 0 thru 7 of Listbox5
Column 9 = Value of TextBox8
Column 10 = Value of TextBox9

There will be a variable number of rows in listbox6 (based on the number of rows in listbox5).

Thanks again Norie.