Message box to input date range

[VQCHEESE]

Heres My scenario:

I have a sheet called "Shipping". I have filters set up on all my columns. But i want a macro to pull up a box where they can enter a date range like 9-28-09 to 10-2-09, this would filter all the data that complies with the data range they inputed in the box. The date range they are typing in would coincide with the column "G"

 

[foverman]

Hi Brian,

You could use a form for you entry box.

An alternate solution would be to have a cell where the user would enter the date. There could be a button they would click to filter, or it could be set that when that cell changes it automatically filters.

Can you see that working?

 

[EducatedFool]

Try this option:

Private Sub B_Дата1_Change_Click()
    Me.Date1 = Format(Get_Date(Me.Date1, Now), "MM-DD-YYYY")
End Sub

Private Sub B_Дата2_Change_Click()
    Me.Date2 = Format(Get_Date(Me.Date2, Now), "MM-DD-YYYY")
End Sub

Private Sub CommandButton1_Click()
    If Me.Date1 = "" Then MsgBox "Enter Date 1", vbCritical, "error": Exit Sub
    If Me.Date2 = "" Then MsgBox "Enter Date 2", vbCritical, "error": Exit Sub
    Range("G1").AutoFilter
    Range("G1").AutoFilter Field:=7, Criteria1:=">=" & CDbl(CDate(Me.Date1)), _
                           Operator:=xlAnd, Criteria2:="<=" & CDbl(CDate(Me.Date2))
End Sub

Private Sub CommandButton2_Click()
    If ActiveSheet.AutoFilterMode Then Range("G1").AutoFilter
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    Me.Date1 = Format(Now, "MM-DD-YYYY")
    Me.Date2 = Format(Now + 500, "MM-DD-YYYY")
End Sub

See the example:

 

[foverman]

EducatedFool

Great looking solution.

What version of Excel are you using? I'm running 2003, and I can set the filter and clear it okay. But when I click the green button to Run Filter, I get an error, The macro Sample__27-09-2009__3-35-04.xls!.. then something in Russian ... cannot be found.

I wonder why it will only run once?

--- Edit

I think it might have to do with the module names being in Russian. It just doesn't to translate well. I also find that the date select form won't show.

 

[EducatedFool]

Try this version:
(I replaced all the Russian names into English)

 

[foverman]

Works great!