note that this will only prompt for the "Shift" cell if it contains
exactly one space character. An alternative that would prompt if it was
actually empty (or had multiple spaces) would be

If Trim(Worksheets("01-01-07").Range("Shift").Value) = "" Then

In article ,
"Bob Phillips" wrote:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ans As Variant

With Worksheets("01-01-07")

If .Range("Date").Value = "" Then
Dans = MsgBox("Have a date been entered? " & _
"If not, please enter adate.", vbYesNo)
If Dans = vbNo Then
Application.Goto Worksheets("01-01-07").Range("Date")
Cancel = True
Exit Sub
End If

End If

If 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
Exit Sub
End If

End If

Application.DisplayAlerts = False
ThisWorkbook.Save
ThisWorkbook.Close
Application.DisplayAlerts = True

End With

End Sub

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Daviv" wrote in message
...
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


--
Thanks!