Solved: Add Message to Message Box if No Matches

[coliervile]

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

[VBA]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[/VBA]

[mdmackillop]

I'm not clear if the test should be inside or outside the loop, but the priciple is the same
[VBA]
Dim Test As Boolean
Test = False
For i = LBound(mpRows) To UBound(mpRows)
If mpRows(i, 1) <> False Then
Test = True
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 Test = False Then MsgBox "NO LEAVE REQUESTED"

[/VBA]

[Bob Phillips]

[vba]

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
Else

MsgBox "No Leave Requested", vbOKOnly + vbInformation
End If
End With
End Sub
[/vba]

[coliervile]

XLD everything is working great this posting is complete and I'll mark it SOLVED.

Best regards,

Charlie