cdate convert problem to filter datas

[elevisse]

Hi All,
I would like to filter datas with textbox date but some computers use different date format. My file doesnt filter with yyyy/mm/dd or mm/dd/yyyy format. For example, I use dd/mm/yyyy format, it works in my computer. Whats the problem? what should I do, it works every formats and Should L4 cell be dd/mm/yyyy format. Please see codes below. Could you please help me. Thanks.

Private Sub CommandButton1_Click()
    
    Application.ScreenUpdating = False
  
    Application.EnableEvents = False
    Dim Son, Devir
       
       
    Range("A3:O" & Rows.Count).AutoFilter Field:=5
    Son = Cells(Rows.Count, 2).End(3).Row
    
    TextBox1.Value = Format(TextBox1.Value, "dd/mm/yyyy")
    
    If TextBox1 <> "" Then
        
        If IsDate(TextBox1) Then
            Devir = WorksheetFunction.SumIf(Range("E5:E" & Son), "<" & CLng(CDate(TextBox1)), Range("H5:H" & Son))
            Range("H4") = Range("H4") + Devir
            
            Range("A3:O" & Rows.Count).AutoFilter Field:=5, Criteria1:="<" & CLng(CDate(TextBox1))
            
            If Cells(Rows.Count, 2).End(3).Row > 3 Then
                Range("A5:O" & Rows.Count).EntireRow.Delete
                Range("A3:O" & Rows.Count).AutoFilter Field:=5
                Cells(Rows.Count, 8).End(3).Offset(2, 0) = WorksheetFunction.Sum(Range("H4:H" & Cells(Rows.Count, 2).End(3).Row))
            End If
            
            Range("A3:O" & Rows.Count).AutoFilter Field:=5
           
           Unload tarih
            
       [L4] = CDate(TextBox1.Value) - 1
       
       Application.CutCopyMode = False
       Application.Run "formulerun"
       
Selection.AutoFilter
                
        Else
            MsgBox "Please enter starting date!", vbCritical
        End If
    Else
        MsgBox "Please enter starting date!", vbCritical
    End If
 
 
   Application.EnableEvents = True
 
 Range("H:H,J:J").Select
    Range("J1").Activate
    Selection.NumberFormat = "#,##0.00"
    Range("A1").Select
 
    Application.ScreenUpdating = True
        
End Sub



Private Sub TextBox1_Change()

End Sub

Private Sub UserForm_initialize()
  TextBox1.Value = Format(Date, "dd/mm/yyyy")
    
End Sub

Sub selectdate ()
startingdate.Show
End Sub

 

[dmt32]

have a look at this article & see if it helps

Excel AutoFilters in VBA Using Dates. Filter by Dates in Excel VBA

Dave

 

[AlphaFrog]

You could use Application.International to determine the regional date setting. Then adjust your code to suit.

[color=darkblue]Sub[/color] Date_Order()
    [color=darkblue]Select[/color] [color=darkblue]Case[/color] Application.International(xlDateOrder)
        [color=darkblue]Case[/color] 0: MsgBox "System date order is month-day-year"
        [color=darkblue]Case[/color] 1: MsgBox "System date order is day-month-year"
        [color=darkblue]Case[/color] 2: MsgBox "System date order is year-month-day"
    [color=darkblue]End[/color] [color=darkblue]Select[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]