Okay...there's nothing on the "Leave Request" worksheet and the listbox on the userform "frmRequest" is empty...now when I use the "Quick Search by Date" button on the userform "frmRequest" and the Search Date userform opens and I enter a date of 12-Mar-2008 and hit the enter button I get the following error: Run-time error '13': Type mismatch. When I click on Debug it goes to the line in the coding that I've colored RED (and is highlighted in YELLOW in the actual coding) as if there's something wrong in this line??? I copied your coding for ""Private Sub TextBox1_AfterUpdate()"" twice to make sure I didn't get something goofed up.
Best regards,
Charlie
Private Sub TextBox1_AfterUpdate()
Dim mpLastRow As Long
Dim mpRows As Variant
Dim mpNames As Range
Dim mpDatesStart As Range
Dim mpDatesEnd As Range
Dim mpTestDate As Date
Dim mpMessage As String
Dim LastRowPrintout As Long
Dim i As Long
With Worksheets("Leave Request")
.Range("A:E").Sort Key1:=.Range("B2"), Order1:=xlAscending, _
Key2:=.Range("D2"), Order2:=xlAscending, _
Header:=xlYes
mpTestDate = CDate(Me.TextBox1.Text)
mpLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set mpDatesStart = .Range("D1").Resize(mpLastRow)
Set mpDatesEnd = .Range("E1").Resize(mpLastRow)
Set mpNames = .Range("A1").Resize(mpLastRow)
mpRows = .Evaluate("IF((" & mpDatesStart.Address & _
"<=--""" & Format(mpTestDate, "yyyy-mm-dd") & """)*" & _
"(" & mpDatesEnd.Address & ">=--""" & _
Format(mpTestDate, "yyyy-mm-dd") & """)," & _
"ROW(" & mpNames.Address & "))")
If IsEmpty(mpRows) Then
MsgBox "No Leave Request For This Date", vbOKOnly + vbInformation
Else
For i = LBound(mpRows) To UBound(mpRows)
If mpRows(i, 1) <> False Then
mpMessage = mpMessage & "Requested" & " " & mpTestDate & " " & _
"Off- " & " " & mpNames.Cells(i, 1).Value & _
" (Leave type: " & mpNames.Cells(i, 3).Value & _
", Requested on: " & mpNames.Cells(i, 2).Text & ")" & vbNewLine & vbNewLine
LastRowPrintout = Worksheets("Printout").Range("A" & Rows.Count).End(xlUp).Row
.Rows(i).Copy Worksheets("Printout").Range("A" & LastRowPrintout + 1)
End If
Next i
If mpMessage <> "" Then
MsgBox mpMessage, vbOKOnly + vbInformation
Else
MsgBox "No Leave Request For This Date", vbOKOnly + vbInformation
End If
End If
.Range("A:E").Sort Key1:=.Range("D2"), Order1:=xlAscending, _
Key2:=.Range("B2"), Order2:=xlAscending, _
Header:=xlYes
End With
End Sub