DTPicker Control

[JoeMicro]

Hi:
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

Thanks

 

[SkipVought]

faq68-5827

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)

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[Andrzejek]

I would try something like this:

If OptionReceived[blue].Value[/blue] = 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[blue].Value[/blue], _
        Operator:=xlAnd, Criteria2:="<=" & DTPicker2[blue].Value[/blue]
    End With
End If

Have fun.

---- Andy

 

[JoeMicro]

Thanks for replying

Skip:
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?

Joe

 

[Andrzejek]

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]

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

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[JoeMicro]

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]

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
    Sheet1.Unprotect
    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

Application.Dialogs(xlDialogPrint).Show

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

Joe

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