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

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

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