Excel Listbox column filling 2

[BobHudson]

I have created a user form called ProjectSelection. On the userform I have a ListBox called PrjList. The listbox will have 5 columns. The example needs to display 30 lines.
In the ProjectSelection code screen I have a module called UserForm_Initialize(). The code for this module follows.


Private Sub UserForm_Initialize()
Dim row As Integer
Application.ScreenUpdating = False 'No screen updates
' Add items to the ListBox
ProjectSelection.PrjList.ColumnCount = 5
ProjectSelection.PrjList.ColumnWidths = ".5in;.5in;.5in;.5in;.5in"
For row = 0 To 30
With ProjectSelection.PrjList
.List(row, 0) = "Row(" & row & &quot"
.List(row, 1) = "Col(2)"
.List(row, 2) = "Col(3)"
.List(row, 3) = "Col(4)"
.List(row, 4) = "Col(5)"
End With
Next row
Next row
ProjectSelection.PrjList.ListIndex = -1 'No item selected
ProjectSelectionValue = -1
Application.ScreenUpdating = True 'Allow screen updates
End Sub


When this module gets invoked by way of the call:
ProjectSelection.Show

I get the following error message in a pop up window.
Run-time error '381'
Could not set the list property. Invalid property array index.


Any ideas on what I am doing wrong?

Thanks

Bob Hudson

 

[Sawatzky]

Dear Bob,

I assume your code should add items to your listbox, if so you need to use the .additem() method of the listbox to do so.

regards Astrid

 

[BobHudson]

Astrid,

The .AddItem appears to be used to add a item to a single. I need to put 5 items into 5 columns. Is there a way to position to a column before using the .AddItem?

Regards,

Bob

 

[Nikita6003]

Hi Bob,

have you tried assigning your list to an array (properly dimensioned to (29,4)) and assigning the array to the

List

property of your combobox?

Cheers
Nikki

 

[rmikesmith]

Bob,

Nikki's solution may work (I've not tried this but am curious), but if you don't need or want to populate an array with these items then use the following. AddItem does add a single item, effectively "creating" a row entry in the ListBox and populating Column 0. After that, add the additional column items using the

.List(row,1) = "Col(2)"

construct, as you were doing.

BTW, turning screen updating off isn't necessary here since the populating of the ListBox goes on behind the scenes before the Userform is displayed.

Regards,
Mike

 

[BobHudson]

Mike,
Your suggestion was what I was looking for. I don't need the table and since I could have a variable number of rows that needed to be displayed, I didn't really want to allocate a table. Thou If I had to I was going to do that thanks to the suggestion by Nikki.

Thanks for everybodies help. I wasted a whole weekend trying to come up with a way to handle the problem.


Bob