Fill 5 Column Listbox from Range
 

I have a command button that I want to use to fill a 5 columned listbox in a
userform. I am getting an error: Could Not Set the List Property, why?

The first row of the listbox is a header row.

Private Sub cmbAddItem_Click()

Dim wksSource As Worksheet
Dim lngLastRow As Long
Dim i As Long

' store data storage worksheet as variable
Set wksSource = ActiveWorkbook.Sheets("Data Storage")

' find last row in ref column in data storage worksheet
lngLastRow = wksSource.Cells(Rows.Count, "A").End(xlUp).Row

With lbxPreview

' clear listbox first
.Clear

' fill listbox from temporary data storage
For i = 126 To lngLastRow
ERROR .List(.ListCount - 1, 0) = wksSource.Cells(i, 1) ' part number
.List(.ListCount - 1, 1) = wksSource.Cells(i, 2) ' part
description
.List(.ListCount - 1, 2) = wksSource.Cells(i, 3) ' unit price
.List(.ListCount - 1, 3) = wksSource.Cells(i, 4) ' qty
.List(.ListCount - 1, 4) = wksSource.Cells(i, 5) ' extended
price
Next
End With

End Sub
--
Cheers,
Ryan

Fill 5 Column Listbox from Range
 

Unless you have ListCount declared as a public variable somewhere, that is
probably the cause or the error. But it you have a list on a sheet, why not
just use the RowSource?

"Ryan H" wrote:

I have a command button that I want to use to fill a 5 columned listbox in a
userform. I am getting an error: Could Not Set the List Property, why?

The first row of the listbox is a header row.

Private Sub cmbAddItem_Click()

Dim wksSource As Worksheet
Dim lngLastRow As Long
Dim i As Long

' store data storage worksheet as variable
Set wksSource = ActiveWorkbook.Sheets("Data Storage")

' find last row in ref column in data storage worksheet
lngLastRow = wksSource.Cells(Rows.Count, "A").End(xlUp).Row

With lbxPreview

' clear listbox first
.Clear

' fill listbox from temporary data storage
For i = 126 To lngLastRow
ERROR .List(.ListCount - 1, 0) = wksSource.Cells(i, 1) ' part number
.List(.ListCount - 1, 1) = wksSource.Cells(i, 2) ' part
description
.List(.ListCount - 1, 2) = wksSource.Cells(i, 3) ' unit price
.List(.ListCount - 1, 3) = wksSource.Cells(i, 4) ' qty
.List(.ListCount - 1, 4) = wksSource.Cells(i, 5) ' extended
price
Next
End With

End Sub
--
Cheers,
Ryan

Fill 5 Column Listbox from Range
 

Because I like to make things hard on myself. That is much better, thanks!
--
Cheers,
Ryan


"JLGWhiz" wrote:

Unless you have ListCount declared as a public variable somewhere, that is
probably the cause or the error. But it you have a list on a sheet, why not
just use the RowSource?

"Ryan H" wrote:

I have a command button that I want to use to fill a 5 columned listbox in a
userform. I am getting an error: Could Not Set the List Property, why?

The first row of the listbox is a header row.

Private Sub cmbAddItem_Click()

Dim wksSource As Worksheet
Dim lngLastRow As Long
Dim i As Long

' store data storage worksheet as variable
Set wksSource = ActiveWorkbook.Sheets("Data Storage")

' find last row in ref column in data storage worksheet
lngLastRow = wksSource.Cells(Rows.Count, "A").End(xlUp).Row

With lbxPreview

' clear listbox first
.Clear

' fill listbox from temporary data storage
For i = 126 To lngLastRow
ERROR .List(.ListCount - 1, 0) = wksSource.Cells(i, 1) ' part number
.List(.ListCount - 1, 1) = wksSource.Cells(i, 2) ' part
description
.List(.ListCount - 1, 2) = wksSource.Cells(i, 3) ' unit price
.List(.ListCount - 1, 3) = wksSource.Cells(i, 4) ' qty
.List(.ListCount - 1, 4) = wksSource.Cells(i, 5) ' extended
price
Next
End With

End Sub
--
Cheers,
Ryan