PDA

View Full Version : Populate Listbox error



Tezzies
05-20-2011, 07:47 AM
Hello all,

Slight problem, trying to populate my listbox in a form. as soon I hit .List(.ListCount - 1, 10) = r.Offset(1, 5).Value I get the error "Could not set the list property.Invalid property value". I have set the Column Count to 16 to no avail.

Sub Load()

Dim r As Range
Dim rRange As Range

Set rRange = Range("Progs1R")
For Each r In rRange
If r.Value <> "" Then
With LbxJobs
.AddItem r.Offset(1, 0).Value
.List(.ListCount - 1, 1) = "|"
.List(.ListCount - 1, 2) = r.Offset(1, 1).Value
.List(.ListCount - 1, 3) = "|"
.List(.ListCount - 1, 4) = r.Offset(1, 2).Value
.List(.ListCount - 1, 5) = "|"
.List(.ListCount - 1, 6) = r.Offset(1, 3).Value
.List(.ListCount - 1, 7) = "|"
.List(.ListCount - 1, 8) = r.Offset(1, 4).Value
.List(.ListCount - 1, 9) = "|"
.List(.ListCount - 1, 10) = r.Offset(1, 5).Value
.List(.ListCount - 1, 11) = "|"
.List(.ListCount - 1, 12) = r.Offset(1, 6).Value
End With
End If
Next r
End Sub


Many thanks in advance

GTO
05-20-2011, 09:21 AM
For an unbound listbox, I believe the limit is ten columns (0 to 9).

Edit: Okay, actually I was pretty sure, but took my blind eyes a bit to find. From vba Help for ColumnCount Property:
ColumnCount Property


Specifies the number of columns to display in a list box or combo box.
Syntax
object.ColumnCount [= Long]
The ColumnCount property syntax has these parts:
PartDescriptionobjectRequired. A valid object.LongOptional. Specifies the number of columns to display.

Remarks
If you set the ColumnCount property for a list box to 3 on an employee form, one column can list last names, another can list first names, and the third can list employee ID numbers.
Setting ColumnCount to 0 displays zero columns, and setting it to -1 displays all the available columns. For an unbound (mk:@MSITStore:C:\Program%20Files\Common%20Files\Microsoft%20Shared\VBA\VBA 6\1033\fm20.chm::/html/IDH_f3defUnbound.htm) data source (mk:@MSITStore:C:\Program%20Files\Common%20Files\Microsoft%20Shared\VBA\VBA 6\1033\fm20.chm::/html/IDH_f3defDataSource.htm), there is a 10-column limit (0 to 9).
You can use the ColumnWidths property to set the width of the columns displayed in the control.

Tezzies
05-22-2011, 04:10 AM
Thanks for the help