Filter by user-submitted date range
 

Excel 2003, Windows XP Pro

I have a client who has customer lists with dates and Balances Due that are
in Excel format. She would like to be able to have an input box pop up when
needed that will prompt her to enter a date range, which will then filter the
current data by that date range, showing only that - or, alternatively,
copying it to its own sheet in the file. The assumption is that the dates
will be different each time she does this. Making the vba Form to allow the
input is no problem, but I'm unsure on the code itself needed to accomplish
this.

Any help would be appreciated.
Thanks,
Eric H Krieger

Filter by user-submitted date range
 

How to filter or copy it it will depend on the layout of the xls
sheet. So you need to show the layyout of the sheet..so we can help
you.

I suggest you used the calendar in excel for inputting the date rather
than a input or text box. You can then use a end date or days or months
offset.

EricKei wrote:
Excel 2003, Windows XP Pro

I have a client who has customer lists with dates and Balances Due that are
in Excel format. She would like to be able to have an input box pop up when
needed that will prompt her to enter a date range, which will then filter the
current data by that date range, showing only that - or, alternatively,
copying it to its own sheet in the file. The assumption is that the dates
will be different each time she does this. Making the vba Form to allow the
input is no problem, but I'm unsure on the code itself needed to accomplish
this.

Any help would be appreciated.
Thanks,
Eric H Krieger

Filter by user-submitted date range
 

OK. Here's some sample data which conforms to the format they want:

CustID Contact LastInvDate LastInvAmt Balance
1 Jim 2/2/2006 $40.00 $200.00
2 Bob 2/5/2006 $155.00 $500.00
3 Jane 3/7/2006 $219.00 $800.00
4 Carl 12/30/2005 $37.00 $99.00
5 Lana 3/17/2006 $45.00 $80.00
6 Will 10/31/2005 $300.00 $1,200.00
7 Dave 11/15/2005 $99.00 $305.00
8 Rick 1/25/2006 $77.00 $123.00
9 Max 1/30/2006 $59.00 $100.00

Calendar style is fine :) I'm always open to suggestions.

Thanks!

Filter by user-submitted date range
 

Eric
Below is some code for you
Label1 .caption = "Start Date" then Label3 ( is the date)
Lable2.Captiopm = "End Date then Label 4 ( is the date)


Add two calendar to you from
calendar1
Calendar2
Add three command buttons
commandbutton1.caption = "Filter"
Commandbutton2.caption = " Close"
Commandbutton3.caption = " Show All"

Here is the code for your userform.

Let us know how you get on?
'-----------------------------------------------
Private Sub Calendar1_Click()
'START DATE
Label3.Caption = VBA.Format(Calendar1.Value, "mm/dd/yyyy")
End Sub

Private Sub Calendar2_Click()
'END DATE
Label4.Caption = VBA.Format(Calendar2.Value, "mm/dd/yyyy")
If Calendar2.Value < Calendar1.Value Or Label3.Caption = "" Then
MsgBox " End date must be after Start Date"
Calendar2.Value = Calendar1.Value
Label4.Caption = VBA.Format(Calendar2.Value, "mm/dd/yyyy")
Else
End If
End Sub



Private Sub CommandButton1_Click()
'Filter button

Application.ScreenUpdating = False
If Calendar2.Value < Calendar1.Value Or Label3.Caption = "" Then
MsgBox " End date must be after Start Date"
Exit Sub
End If
Cells.Select
Selection.EntireRow.Hidden = False
If Label3.Caption < "" And Label4.Caption < "" Then

For i = 2 To 1000
Cells(i, 3).Value = VBA.Format(Cells(i, 3).Value, "mm/dd/yyyy")

Cells(i, 3).Select
If Cells(i, 3).Value < "" Then

If CDate(Cells(i, 3).Value) <
CDate(Label3.Caption) Then
Cells(i, 3).Select
Selection.EntireRow.Hidden = True
Else
If CDate(Cells(i, 3).Value)
CDate(Label4.Caption) Then

Cells(i, 3).Select
Selection.EntireRow.Hidden = True
Else
End If
End If
Else

End If
Next i
Else
MsgBox " Select Start Date and End date Before attempting to Filter"


End If
Application.ScreenUpdating = True
End Sub



Private Sub CommandButton2_Click()
' CLOSE Command Buttom
Unload Me
End Sub



Private Sub CommandButton3_Click()
Cells.Select
Selection.EntireRow.Hidden = False
Cells(2, 1).Select
End Sub

Private Sub UserForm_Click()
Calendar1.Value = Date
Calendar2.Value = Date

End Sub
EricKei wrote:
OK. Here's some sample data which conforms to the format they want:

CustID Contact LastInvDate LastInvAmt Balance
1 Jim 2/2/2006 $40.00 $200.00
2 Bob 2/5/2006 $155.00 $500.00
3 Jane 3/7/2006 $219.00 $800.00
4 Carl 12/30/2005 $37.00 $99.00
5 Lana 3/17/2006 $45.00 $80.00
6 Will 10/31/2005 $300.00 $1,200.00
7 Dave 11/15/2005 $99.00 $305.00
8 Rick 1/25/2006 $77.00 $123.00
9 Max 1/30/2006 $59.00 $100.00

Calendar style is fine :) I'm always open to suggestions.

Thanks!

Filter by user-submitted date range
 

Thank you!

The code worked, with only minor tweaking needed to custimize it to what I
needed. :)