PDA

View Full Version : Solved: Add Message to Message Box if No Matches



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

mdmackillop
02-16-2008, 04:55 PM
I'm not clear if the test should be inside or outside the loop, but the priciple is the same

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"

Bob Phillips
02-16-2008, 04:59 PM
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

coliervile
02-16-2008, 07:49 PM
XLD everything is working great this posting is complete and I'll mark it SOLVED.

Best regards,

Charlie