| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill a Listbox with mult. Variables in userform
Hi Guys and Gals,
Here is the scoop. I have a userform that has 1 combo box and 2 date time pickers and 1 listbox. So far i have it so the list box is filled from what the user chose in the combobox (cboGrower), no problems there. But i would then like the user to choose a start date and a finish date and only the growers information that falls within those dates fill the list box. The following is what i have so far. Thank you for any help you can offer! Private Sub UserForm_Initialize() Set source = ThisWorkbook.Names.Item("Database").RefersToRange LoadGrower End Sub Private Sub LoadData() 'lstData is the list box With lstData .Clear grower = cbogrower.Value Start = dtStart.Value Finish = dtFinish.Value For index = 2 To source.Rows.Count If grower = source.Cells(index, 4) Then .AddItem source.Cells(index, 1) ' ID .List(.ListCount - 1, 2) = source.Cells(index, eBoxes.ProduceItem) 'Date .List(.ListCount - 1, 3) = source.Cells(index, eBoxes.BoxesPurchased) ' Fruit .List(.ListCount - 1, 4) = source.Cells(index, eBoxes.Date) 'Boxes .List(.ListCount - 1, 5) = source.Cells(index, eBoxes.Inv) 'Boxes .List(.ListCount - 1, 6) = source.Cells(index, eBoxes.GrossPrice) 'Boxes .List(.ListCount - 1, 7) = source.Cells(index, eBoxes.Frt) 'Boxes ' .List(.ListCount - 1, 8) = source.Cells(index, eBoxes.NetGross) 'Boxes ' .List(.ListCount - 1, 9) = source.Cells(index, eBoxes.FormID) 'Boxes End If Next End With End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fill a Listbox with mult. Variables in userform
If grower = source.Cells(index, 4) Then
would have additional conditions if grower = source.Cells(index,4) and _ source.Cells(index,5) = cdate(dtStart.Value) and _ source.cells(index,5) <=cdate(dtFinish.Value) then You could use Start and Finish in the statement instead. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "Jennifer" wrote: Hi Guys and Gals, Here is the scoop. I have a userform that has 1 combo box and 2 date time pickers and 1 listbox. So far i have it so the list box is filled from what the user chose in the combobox (cboGrower), no problems there. But i would then like the user to choose a start date and a finish date and only the growers information that falls within those dates fill the list box. The following is what i have so far. Thank you for any help you can offer! Private Sub UserForm_Initialize() Set source = ThisWorkbook.Names.Item("Database").RefersToRange LoadGrower End Sub Private Sub LoadData() 'lstData is the list box With lstData .Clear grower = cbogrower.Value Start = dtStart.Value Finish = dtFinish.Value For index = 2 To source.Rows.Count If grower = source.Cells(index, 4) Then .AddItem source.Cells(index, 1) ' ID .List(.ListCount - 1, 2) = source.Cells(index, eBoxes.ProduceItem) 'Date .List(.ListCount - 1, 3) = source.Cells(index, eBoxes.BoxesPurchased) ' Fruit .List(.ListCount - 1, 4) = source.Cells(index, eBoxes.Date) 'Boxes .List(.ListCount - 1, 5) = source.Cells(index, eBoxes.Inv) 'Boxes .List(.ListCount - 1, 6) = source.Cells(index, eBoxes.GrossPrice) 'Boxes .List(.ListCount - 1, 7) = source.Cells(index, eBoxes.Frt) 'Boxes ' .List(.ListCount - 1, 8) = source.Cells(index, eBoxes.NetGross) 'Boxes ' .List(.ListCount - 1, 9) = source.Cells(index, eBoxes.FormID) 'Boxes End If Next End With End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Fill a Listbox with mult. Variables in userform
Thanks for the help Tom!
I put what you wrote in the following, not sure if that is correct. Also, the form will open but nothing fills the listbox when i use the combobox. Private Sub LoadData() If grower = source.Cells(index, 4) Then If grower = source.Cells(index, 4) And _ source.Cells(index, 3) = CDate(dtStart.Value) And _ source.Cells(index, 3) <= CDate(dtFinish.Value) Then With lstData .Clear grower = cbogrower.Value For index = 2 To source.Rows.Count If grower = source.Cells(index, 4) Then .AddItem source.Cells(index, 1) ' ID .List(.ListCount - 1, 2) = source.Cells(index, eBoxes.ProduceItem) 'Date .List(.ListCount - 1, 3) = source.Cells(index, eBoxes.BoxesPurchased) ' Fruit .List(.ListCount - 1, 4) = source.Cells(index, eBoxes.Date) 'Boxes .List(.ListCount - 1, 5) = source.Cells(index, eBoxes.Inv) 'Boxes .List(.ListCount - 1, 6) = source.Cells(index, eBoxes.GrossPrice) 'Boxes .List(.ListCount - 1, 7) = source.Cells(index, eBoxes.Frt) 'Boxes ' .List(.ListCount - 1, 8) = source.Cells(index, eBoxes.NetGross) 'Boxes ' .List(.ListCount - 1, 9) = source.Cells(index, eBoxes.FormID) 'Boxes End If Next End With End If End If End Sub -- Though daily learning, I LOVE EXCEL! Jennifer "Tom Ogilvy" wrote: If grower = source.Cells(index, 4) Then would have additional conditions if grower = source.Cells(index,4) and _ source.Cells(index,5) = cdate(dtStart.Value) and _ source.cells(index,5) <=cdate(dtFinish.Value) then You could use Start and Finish in the statement instead. -- Regards, Tom Ogilvy -- Regards, Tom Ogilvy "Jennifer" wrote: Hi Guys and Gals, Here is the scoop. I have a userform that has 1 combo box and 2 date time pickers and 1 listbox. So far i have it so the list box is filled from what the user chose in the combobox (cboGrower), no problems there. But i would then like the user to choose a start date and a finish date and only the growers information that falls within those dates fill the list box. The following is what i have so far. Thank you for any help you can offer! Private Sub UserForm_Initialize() Set source = ThisWorkbook.Names.Item("Database").RefersToRange LoadGrower End Sub Private Sub LoadData() 'lstData is the list box With lstData .Clear grower = cbogrower.Value Start = dtStart.Value Finish = dtFinish.Value For index = 2 To source.Rows.Count If grower = source.Cells(index, 4) Then .AddItem source.Cells(index, 1) ' ID .List(.ListCount - 1, 2) = source.Cells(index, eBoxes.ProduceItem) 'Date .List(.ListCount - 1, 3) = source.Cells(index, eBoxes.BoxesPurchased) ' Fruit .List(.ListCount - 1, 4) = source.Cells(index, eBoxes.Date) 'Boxes .List(.ListCount - 1, 5) = source.Cells(index, eBoxes.Inv) 'Boxes .List(.ListCount - 1, 6) = source.Cells(index, eBoxes.GrossPrice) 'Boxes .List(.ListCount - 1, 7) = source.Cells(index, eBoxes.Frt) 'Boxes ' .List(.ListCount - 1, 8) = source.Cells(index, eBoxes.NetGross) 'Boxes ' .List(.ListCount - 1, 9) = source.Cells(index, eBoxes.FormID) 'Boxes End If Next End With End Sub -- Though daily learning, I LOVE EXCEL! Jennifer |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Userform and variables | Excel Discussion (Misc queries) | |||
| userform listbox cannot get listbox.value to transfer back to main sub | Excel Programming | |||
| UserForm with ListBox | Excel Programming | |||
| listbox and variables | Excel Programming | |||
| UserForm ListBox | Excel Programming | |||
Linear Mode
