coliervile
02-16-2008, 04:19 PM
In the following macro (complements of "XLD") I want to add a message to the message box of "NO LEAVE REQUESTED" if there are no matches found??? I've played around with it, but can't figure out how to get it to display the message.
Best regards,
Charlie
Private Sub TextBox1_AfterUpdate()
Dim mpLastRow As Long
Dim mpRows As Variant
Dim mpNames As Range
Dim mpDatesStart As Range
Dim mpEndStart As Range
Dim mpTestDate As Date
Dim mpMessage As String
Dim i As Long
With Worksheets("Leave Request")
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 & "))")
For i = LBound(mpRows) To UBound(mpRows)
If mpRows(i, 1) <> False Then
mpMessage = mpMessage & mpNames.Cells(i, 1).Value & _
" (Leave type: " & mpNames.Cells(i, 3).Value & _
", requested on: " & mpNames.Cells(i, 2).Text & ")" & vbNewLine & vbNewLine
End If
Next i
If mpMessage <> "" Then MsgBox mpMessage, vbOKOnly + vbInformation
End With
End Sub
Best regards,
Charlie
Private Sub TextBox1_AfterUpdate()
Dim mpLastRow As Long
Dim mpRows As Variant
Dim mpNames As Range
Dim mpDatesStart As Range
Dim mpEndStart As Range
Dim mpTestDate As Date
Dim mpMessage As String
Dim i As Long
With Worksheets("Leave Request")
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 & "))")
For i = LBound(mpRows) To UBound(mpRows)
If mpRows(i, 1) <> False Then
mpMessage = mpMessage & mpNames.Cells(i, 1).Value & _
" (Leave type: " & mpNames.Cells(i, 3).Value & _
", requested on: " & mpNames.Cells(i, 2).Text & ")" & vbNewLine & vbNewLine
End If
Next i
If mpMessage <> "" Then MsgBox mpMessage, vbOKOnly + vbInformation
End With
End Sub