+ Reply to Thread
Results 1 to 3 of 3

Fill a Listbox with mult. Variables in userform

  1. #1
    Jennifer
    Guest

    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. #2
    Tom Ogilvy
    Guest

    RE: 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. #3
    Jennifer
    Guest

    RE: 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1