Load data into a listbox
 

It's me again!!!!!!!!
I chose a market from combo box in a form and it fills this list box.
Problem arises in column 10 & 11 which have formulas in them. I get a bug
when i try to run. Can it just display the value of these formulas? Also, how
do you make it so that the user can not change these two columns in the
listbox. Note, I want them to be able to update the other values though. Once
again. Thank you!
Private Sub LoadData()
txtDataID = ""
txtBoxes = ""
With lstData
.Clear
market = cmbMarket.Value
For index = 2 To source.Rows.Count
If market = source.Cells(index, 11) Then
.AddItem source.Cells(index, 1) ' ID
.List(.ListCount - 1, 1) = source.Cells(index, 1) 'Index
.List(.ListCount - 1, 2) = source.Cells(index, 2) 'Date
.List(.ListCount - 1, 3) = source.Cells(index, 3) 'Fruit
.List(.ListCount - 1, 4) = source.Cells(index, 5) 'Boxes to
Market
.List(.ListCount - 1, 5) = source.Cells(index, 6) 'Boxes
transfered to another market
.List(.ListCount - 1, 6) = source.Cells(index, 7) 'Boxes
Rtn. to Inventory
.List(.ListCount - 1, 7) = source.Cells(index, 8) 'Boxes of
Waste
.List(.ListCount - 1, 8) = source.Cells(index, 9) 'Box weight
.List(.ListCount - 1, 9) = source.Cells(index, 10) 'Price
per pound
.List(.ListCount - 1, 10) = source.Cells(index, 15) 'Price
per Box ******
.List(.ListCount - 1, 11) = source.Cells(index, 16)
'Potential Sales*****
.List(.ListCount - 1, 12) = source.Cells(index, 13) 'Storage
Location

End If
Next
End With
End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer

Load data into a listbox
 

First, I would stay away from variables named Source and Index. And it looks
like you're adding the first value twice to each item in the listbox.

But I think the real problem comes from the number of columns you have in your
listbox.

Take a look at .columnCount in VBA's help.

From xl2002:

Setting ColumnCount to 0 displays zero columns, and setting it to -1 displays
all the available columns. For an unbound data source, there is a 10-column
limit (0 to 9).

==
So if you want more, maybe you can copy the values that match to another
(temporary) worksheet and pick it up from there.



Jennifer wrote:

It's me again!!!!!!!!
I chose a market from combo box in a form and it fills this list box.
Problem arises in column 10 & 11 which have formulas in them. I get a bug
when i try to run. Can it just display the value of these formulas? Also, how
do you make it so that the user can not change these two columns in the
listbox. Note, I want them to be able to update the other values though. Once
again. Thank you!
Private Sub LoadData()
txtDataID = ""
txtBoxes = ""
With lstData
.Clear
market = cmbMarket.Value
For index = 2 To source.Rows.Count
If market = source.Cells(index, 11) Then
.AddItem source.Cells(index, 1) ' ID
.List(.ListCount - 1, 1) = source.Cells(index, 1) 'Index
.List(.ListCount - 1, 2) = source.Cells(index, 2) 'Date
.List(.ListCount - 1, 3) = source.Cells(index, 3) 'Fruit
.List(.ListCount - 1, 4) = source.Cells(index, 5) 'Boxes to
Market
.List(.ListCount - 1, 5) = source.Cells(index, 6) 'Boxes
transfered to another market
.List(.ListCount - 1, 6) = source.Cells(index, 7) 'Boxes
Rtn. to Inventory
.List(.ListCount - 1, 7) = source.Cells(index, 8) 'Boxes of
Waste
.List(.ListCount - 1, 8) = source.Cells(index, 9) 'Box weight
.List(.ListCount - 1, 9) = source.Cells(index, 10) 'Price
per pound
.List(.ListCount - 1, 10) = source.Cells(index, 15) 'Price
per Box ******
.List(.ListCount - 1, 11) = source.Cells(index, 16)
'Potential Sales*****
.List(.ListCount - 1, 12) = source.Cells(index, 13) 'Storage
Location

End If
Next
End With
End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer

--

Dave Peterson