Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to load data into excel? | Excel Discussion (Misc queries) | |||
How to load a listbox in a userform? | Excel Discussion (Misc queries) | |||
Listbox initialized on workbook load | Excel Programming | |||
Qn: Load Data into UserForm1?? | Excel Programming | |||
listbox.value not equal to listbox.list(listbox.listindex,0) | Excel Programming |