selecting a date range from userform and sorting the range on worksheet

[kbishop94]

I have 2 txt boxes on a form which are datepickers. txtDateBox2 is for entering the start date, and txtDateBox3 is for enterning the ending date.

When cmdGetReport button is selected I need the code to filter the dates that are in column B on the worksheet ("Seatex Incident Log") in the set range based on those 2 dates. I cant figure out why I am getting error '448' Named argument not found. ?? Thanks for any help in getting this working for me.

Here is my code:

Private Sub cmdGetReport_Click()
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim rCol As Long
Dim ws As Worksheet
Dim W1Startdate As String, W1Enddate As String

Set ws = Worksheets("Seatex Incident Log")

W1Startdate = Format(Date, "mm/dd/yyyy")
W1Enddate = Format(Date, "mm/dd/yyyy")

Me.txtDateBox2 = W1Startdate        ' "FROM" date
Me.txtDateBox3 = W1Enddate          ' "TO" date

rCol = Sheets("Seatex Incident Log").UsedRange.Rows.Count

Range(Cells(18, 1), Cells(rCol, 29)).Select
Range(Cells(18, 1), Cells(rCol, 29)).Activate
ActiveWorkbook.Worksheets("Seatex Incident Log").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Seatex Incident Log").AutoFilter Field:=2, Criteria1:=">=" & W1Startdate, Criteria2:="<=" & W1Enddate

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

 

[dmt32]

Hi,
You say controls Me.txtDateBox2 and Me.txtDateBox3 are date pickers but you have used naming convention that suggests they are just textboxes where you manually enter the date rather than pick a date from a picker control?


If so, untested but try this update to your code & see if will do what you want.

 Private Sub cmdGetReport_Click()

    Dim wsSeatexIncidentLog As Worksheet
    Dim W1Startdate As String, W1Enddate As String
    
'set object variable to worksheet
    Set wsSeatexIncidentLog = ThisWorkbook.Worksheets("Seatex Incident Log")
    
    
     W1Startdate = Me.txtDateBox2.Value      ' "FROM" date
     W1Enddate = Me.txtDateBox3.Value        ' "TO" date
    
'ensure text entered looks like a date
    If W1Startdate Like "##/##/####" And W1Enddate Like "##/##/####" Then
    
     With wsSeatexIncidentLog
'if sheet protected, unprotect it - add password if required
        .Unprotect Password:=""
        .Sort.SortFields.Clear
        With .Range("A18").CurrentRegion
'clear filter
        .AutoFilter
        .AutoFilter Field:=2, _
                    Criteria1:=">=" & CLng(DateValue(W1Startdate)), _
                    Criteria2:="<=" & CLng(DateValue(W1Enddate))
        End With
    End With
    End If


End Sub

Dave

 

[kbishop94]

Hi,
You say controls Me.txtDateBox2 and Me.txtDateBox3 are date pickers but you have used naming convention that suggests they are just textboxes where you manually enter the date rather than pick a date from a picker control?

If so, untested but try this update to your code & see if will do what you want.

Dave

THanks, Dave. THat code worked for me!!


the date picker I am using isnt the DTPicker but another one that I found on here (mrexcel)that I was able to implement into my workbook so that other machines in my company that will access this workbook but are running 64bit based computers can use it without crashingor errors.

Here is my final code and how I slightly changed it up in order for it to work for me (I was off on just a few minor details like my range needed to start at 17 and not 18 like I thought.)

Private Sub cmdGetReport_Click()

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim rCol As Long
Dim ws As Worksheet
Dim W1Startdate As String, W1Enddate As String

'set object variable to worksheet
    Set ws = ThisWorkbook.Worksheets("Seatex Incident Log")

W1Startdate = Me.txtDateBox2.Value      ' "FROM" date
W1Enddate = Me.txtDateBox3.Value        ' "TO" date

rCol = Sheets("Seatex Incident Log").UsedRange.Rows.Count
    
     With ws
ActiveWorkbook.Worksheets("Seatex Incident Log").Activate
        .Sort.SortFields.Clear

        With .Range(Cells(17, 1), Cells(rCol, 29))
'clear filter
        .AutoFilter
        .AutoFilter Field:=2, _
                    Criteria1:=">=" & CLng(DateValue(W1Startdate)), _
                    Criteria2:="<=" & CLng(DateValue(W1Enddate))
        End With
    End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Thanks again!