Ed

You could put some code in the ThisWorkbook code module. I have used the
Workbook_BeforeClose() Event. To enter this code, right click on the Excel
Icon in the top left of the workbook and select view code. It goes in the
resultant pane.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim myCell As Range
For Each myCell In Worksheets("Sheet1").Range("A1:A4")
If IsEmpty(myCell.Value) Then
MsgBox "You have not filled in all cells, you cannot close the workbook"
Cancel = True
Exit Sub
End If
Next myCell
End Sub

It looks at the range A1:A4 in sheet1 and if there are any blanks it
displays a message box and cancels the close.

Iteration of the range may not be the best way to handle large areas of
data, but if you have this post back and we could use SpecialCells or
similar. If you want to stick with iteration of the range and it is a lot
of data, you may wish to put another message box at the start of the code,
warning users the workbook is being checked for blanks, or use a status bar
message.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"universal" wrote in message
...
Currently I have an excel file that goes to users to enter data which I
then collate.

There is a hidden column at the right of all the data entry spaces that
checks for invalid responses or blank cells and highlights them.
Unfortunately I still get files back from users with some empty cells.

I spend hours opening these files, realising they're useless and have
to resend them to the original user.

Is there any way, then, of being even more obstructive than Iam
currently, so that users cannot even save the file or close Excel while
there remain gaps in there data?

Thanks in advance,
Ed



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/