Hi
Try this code. You will need to move the data validation entries to a separate sheet or the code will keep asking you to enter values in Columns B, E and K right down to row 165.
Hope this helps.
Good luck.
Tony
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim count1 As Integer
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then
'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End If
For count1 = 8 To LastRow
If Sheets("Input").Range("C" & count1) <> vbNullString Then
If Sheets("Input").Range("B" & count1) = vbNullString Then
MsgBox "You must enter todays date in Cell B" & count1 & ". Please Revise", vbOKOnly, "Oops"
Range("B" & count1).Select
GoTo Finished
End If
End If
If Sheets("Input").Range("C" & count1) <> vbNullString Then
If Sheets("Input").Range("E" & count1) = vbNullString Then
MsgBox "You must enter FC Name in Cell E" & count1 & ". Please Revise", vbOKOnly, "Oops"
Range("E" & count1).Select
GoTo Finished
End If
End If
If Sheets("Input").Range("C" & count1) <> vbNullString Then
If Sheets("Input").Range("K" & count1) = vbNullString Then
MsgBox "You must enter a Status in Cell K" & count1 & ". Please Revise", vbOKOnly, "Oops"
Range("K" & count1).Select
GoTo Finished
End If
End If
Next count1
Finished:
End Sub
Bookmarks