Preventing File Closing if certain cells are empthy

[JohnH]

Is there a way to prevent the user from exiting or saving a file if certain cells are empty? I am creating a template to move data into a database but I want to make sure the user has entered all the required fields before saving and exiting the program.

Thanks.

John

[Tom Schreiner]

Sure...
You did not give any details...
This will not allow an exit if there is
nothing in sheet1 Cell A1...
It will select the cell in question as well.
You could test a range, mutliple ranges, data types, formats, ect...

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Sheet1.Cells(1, 1) = Empty Or Sheet1.Cells(1, 1) = "" Then

    Cancel = True

    MsgBox "Required field missing"

    Sheet1.Cells(1, 1).Select

End If

End Sub

[ This Message was edited by: TsTom on 2002-04-30 08:40 ]

[Joe Was]

Try this. JSW

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim mySel
'Works in "ThisWorkBook" module.
'Only if cell has value will the Save or SaveAs utility open.
'If cell has no value, no "SaveAs" is acknowledged.
'If cell has no value "Save" by choice only.
If Sheets("Sheet1").Range("A1") = "" Then
mySel = MsgBox("Do you really want to save the workbook," & Chr(13) _
& "the required cell A1 is empty?", vbYesNo)
If mySel = vbNo Then Cancel = True
End If
End Sub

The above code is safe.

The code below, will prevent a "Close" if the condition is not met, so use caution!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Prevent workbook close if cell is empty.
If Sheets("Sheet1").Range("A1") = "" Then Cancel = True
End Sub

[GUS]

Do you think is any way to:

Prevent a worksheet/file to print if certain cells are empty?