Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
range as listbox column | Excel Programming | |||
Fill a multicolumn listbox with data from a range in another works | Excel Programming | |||
populate 2 column listbox from one named range | Excel Programming | |||
ListBox "dynamic fill range" | Excel Programming | |||
Fill a listbox with data from variable range | Excel Programming |