Smart questions
Smart answers
Smart people
Join Tek-Tips Forums

Member Login

Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

JoeMicro (TechnicalUser) (OP)
14 Jan 08 2:31
I M trying a 2 DTPicker Cohtrols on a user form  to filter a sheet, but for some reason it filters for the date 1/0/1900, i did use DTPicker control already a few times an it works well, in fact on the same sheet i have got another user form with a DTPicker control on it and it works great, but for some reason, on this form it wont give me the desired value..


If OptionReceived = True Then
        With Sheet1.Range("A1").End(xlUp)
            .AutoFilter field:=1, Criteria1:=">=" & DTPicker1, _
            Operator:=xlAnd, Criteria2:="<=" & DTPicker2
        End With
    End If
SkipVought (Programmer)
14 Jan 08 8:36

FAQ68-5827: Why do Dates and Times seem to be so much trouble?

If you look at the Filter Criteria on the sheet, what dates do you see?

Do you have REAL DATES in column A on your sheet? (if you chnage the FORMAT to GENERAL, the DISPLAY will change for REAL DATES)


glasses When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Largetongue

Andrzejek (Programmer)
14 Jan 08 12:02
I would try something like this:


If OptionReceived.Value = True Then
    Debug.Print "Date 1 is: " & DTPicker1.Value
    Debug.Print "Date 2 is: " & DTPicker2.Value
    With Sheet1.Range("A1").End(xlUp)
        .AutoFilter field:=1, Criteria1:=">=" & DTPicker1.Value, _
        Operator:=xlAnd, Criteria2:="<=" & DTPicker2.Value
    End With
End If

Have fun.

---- Andy

JoeMicro (TechnicalUser) (OP)
15 Jan 08 1:01
Thanks for replying

Yes when i change the format on the sheet it turns into a number, but i tried to play around with the code


Criteria1:=">=" & Format(DTPicker1, "DDMMMYY")
didn't help me much, instead of  the value "1/0/1900" it gave me "12/31/1999", no matter what date i set the DTPicker.
i also tried this


Sheet3.Range("A2") = DTPicker1.Value
gave me a "12:00 AM" which in numeric value is 0.

and Andy:
i tried your code didn't change anything.
what is it suppose to do anyway?


Andrzejek (Programmer)
15 Jan 08 8:28
My code does not change anything, it just gives you some information of what your DTPickers are 'picking' - if they point to the right dates, so you can check if your .AutoFilter has any chance to work properly.

Also, it would be nice to avoid 'default' properties of controls in the code (bad coding, IMHO)

That's all.

Have fun.

---- Andy

SkipVought (Programmer)
15 Jan 08 9:27

Try macro recording, changing the date criteria manually in the auto filter, to see exactly what the DATE looks like.


glasses When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Largetongue

JoeMicro (TechnicalUser) (OP)
15 Jan 08 23:06
that's what the macro recorder did


Selection.AutoFilter field:=1, Criteria1:=">=11/1/2007", Operator:=xlAnd _
        , Criteria2:="<=11/26/2007"
so i tried to change to


(DTPicker1, "MMDDYY")
but still no lock
JoeMicro (TechnicalUser) (OP)
15 Jan 08 23:55
OK i have got the Answer, I M Really sorry guys, its my fault, i didn't give you the complete code the problem was this:


Application.ScreenUpdating = False
    Unload UserForm1
    'Option received
    If OptionReceived.Value = True Then
        With Sheet1.Range("A1").End(xlUp)
            .AutoFilter Field:=1, Criteria1:=">=" & DTPicker1, _
            Operator:=xlAnd, Criteria2:="<=" & DTPicker2
        End With
            End If

i did


unload userform1
to early,

i did the unload command because i was calling at the end of the code the


so in case the user decides to do Print Preview, he wont be able to do anything because the userform is still running, so i needed to unload, but i did it a little to early.

i m Stupid Sorry


P.S. there is no smart one as one who learns from their mistakes,
(but you could learn from others)

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close