Hmm... it would seem better to me to check Date and Shift directly,
rather than asking the user (especially if they can't check to see if
they have or not). This is one way do that:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Const csTITLE As String = "Validation Check"
Dim vAnswer As Variant
With Worksheets("01-01-07")
With .Range("Date")
If Not IsDate(.Value) Then
Do
vAnswer = Application.InputBox( _
Prompt:="Error: Enter Date:", _
Title:="Validation Check", _
Default:=Date)
If vAnswer = Cancel Then 'user cancelled
Cancel = True
Exit Sub
End If
Loop Until IsDate(vAnswer)
.Value = vAnswer
End If
End With
With .Range("Shift")
If Len(Trim(.Value)) = 0 Then
Do
vAnswer = Application.InputBox( _
Prompt:="Error: Enter Shift", _
Title:=csTITLE, _
Default:=1)
If vAnswer = False Then 'user cancelled
Cancel = True
Exit Sub
End If
Loop Until vAnswer < vbNullString
End If
.Value = vAnswer
End With
End With
Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub





In article ,
Daviv wrote:

I want to write a sub to do the following when a user closes the worksheet:

1) Using a message box, check if data has been entered into cell named
"Date". If the user answers yes, save the file. If the user answers no,
goto the "Date" cell.
2) Using a message box, check if data has been entered into cell named
"Shift". If the user answers yes, save the file. If the user answers no,
goto the "Shift" cell.

The code just check for the "Date" Cell but not for the "Shift" Cell.
Actually, there are 8 other cells in my worksheet that I want to check.
Please 1) check my code below, and 2) offer any suggestions on how to write a
better code.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim ans As Variant

If Worksheets("01-01-07").Range("Date").Value = " " Then
Dans = MsgBox("Have a date been entered? If not, please enter a
date.", vbYesNo)
If Dans = vbNo Then
Application.Goto Worksheets("01-01-07").Range("Date")
Cancel = True
ElseIf Worksheets("01-01-07").Range("Shift").Value = " " Then
Sans = MsgBox("Have a work shift been entered? If not, please enter
a work shift.", vbYesNo)
If Sans = vbNo Then
Application.Goto Worksheets("01-01-07").Range("Shift")
Cancel = True
Else
Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close
Application.DisplayAlerts = True
End If
End If
End If
End Sub