Hello Everyone,
I am looking for code that will verify the date in a certain field is valid. (i.e. nobody uses 99-99-9999) then check the next field that contains a range of checklists that cannot contain blanks. If a blank is discovered then a message pops up stating "Tasks in the checklist are blank. Please complete entire list." and ends the all macros. If all blanks are filled then it will call an email macro that I've already created. The problems I've encountered is that it has to loop through all cells in a range to check for blanks, send the error message AND call the email macro if there aren't any errors. My code seems to call the email macro even if there are errors and it creates several emails.
I haven't been able to find a good way to check the date and have tried several combinations for the other but below are a couple of samples of what I've created.
Sample 1
Sub Check_Blanks()
Dim rngCheck As Range
Dim iCell As Range
Set rngCheck = ActiveSheet.Range("Form_Task")
For Each iCell In rngCheck
If IsEmpty(iCell) Then
iCell.Select
MsgBox ("Tasks in the checklist are blank. Please complete entire list.")
Exit Sub
Else
Mail_Form
End If
Next iCell
End Sub
Sample 2
Sub Check_Blanks2()
Dim rCell As Range
Dim lRow As Long
lRow = 0
For Each rCell In Range("Form_Task")
If rCell = "" Then
If lRow = 0 Then
MsgBox ("Tasks in the checklist are blank. Please complete entire list.")
Exit Sub
End If
ElseIf rCell <> "" Then
MsgBox "PM Form is ready to email"
End If
Mail_Form
Next rCell
End Sub
Bookmarks