I understand what you said has to be done but the macro i need has to check 4
sheets within the workbook for blank cells. The macro would check up to 63
cells in each sheet and in the last two sheets, it would verify that there is
data in at least 1 cell (in a group of cells) in each of two different
sections of the sheet. This is what i have. I just started writing macros two
days ago so bare with me. This might be overkill. When i try to close the
workbook it perform the check but it brings up the code and highlights the
third line in yellow. Does anyone have the patience to help me with this?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Sheets("EOS")
If Application.CountA(.Range("B7, B9, B11, K7, C13, C17, C18, C19, C20, C23,
C24, C25, C29, G17, G27, G37, C44, C45, C49, C50, C55, C56, A60, G46, G55,
I63, I64, F75, F76, F77, F78, F79, F80, F81, F82, F83, F84, F85, F86, G75,
G76, G77, G78, G79, G80, G81, G82, G83, G84, G85, G86, H75, H76, H77, H78,
H79, H80, H81, H82, H83, H84, H85, H86")) < 63 Then
MsgBox "All of the report must be completed first!", 16, "Blank Cells"
Cancel = True
End If
End With
With Sheets("SEM Errors")
If Application.CountA(.Range("B6, B7, B8, C18, C19, C20, C22, C24, C25, C26,
C27, C29, C30, C31, C33, C34, C36, C37, C38, C39, C40, C42, C44, C45, C47,
C49, C50, C51, C53, C55, C56, C58, C59, C60, C61, C63, C65, C67")) < 38 Then
MsgBox "All of the report must be completed first!", 16, "Blank Cells"
Cancel = True
End If
End With
With Sheets("DEV Log")
If Application.CountA(.Range("A10, A11, A12, A13, A14, A15, A16, A17, A18,
A19, A20, B10, B11, B12, B13, B14, B15, B16, B17, B18, B19, B20, C10, C11,
C12, C13, C14, C15, C16, C17, C18, C19, C20")) < 1 Then
MsgBox "All of the report must be completed first!", 16, "Blank Cells"
Cancel = True
End If
End With
With Sheets("DEV Log")
If Application.CountA(.Range("A27, A28, A29, A30, A31, A32, A33, A34, A35,
A36, A37, B27, B28, B29, B30, B31, B32, B33, B34, B35, B36, B37, C27, C28,
C29, C30, C31, C32, C33, C34, C35, C36, C37")) < 1 Then
MsgBox "All of the report must be completed first!", 16, "Blank Cells"
Cancel = True
End If
End With
With Sheets("NMX Errors")
If Application.CountA(.Range("A10, A11, A12, A13, A14, A15, A16, A17, A18,
A19, A20, B10, B11, B12, B13, B14, B15, B16, B17, B18, B19, B20, C10, C11,
C12, C13, C14, C15, C16, C17, C18, C19, C20")) < 1 Then
MsgBox "All of the report must be completed first!", 16, "Blank Cells"
Cancel = True
End If
End With
With Sheets("NMX Errors")
If Application.CountA(.Range("A35, A36, A37, A38, A39, A40, A41, A42, A43,
A36, A37, B27, B28, B29, B30, B31, B32, B33, B34, B35, B36, B37, C27, C28,
C29, C30, C31, C32, C33, C34, C35, C36, C37")) < 1 Then
MsgBox "All of the report must be completed first!", 16, "Blank Cells"
Cancel = True
End If
End With
End Sub

"Otto Moehrbach" wrote:

Which event do you want to use as the trigger for the check, Close or Save?
I will guess Save. I will also assume the cells in question are A1:A5.
Also, I used the name "TheSheetName" as the name of the pertinent sheet in
your file. The following macro will do what you want.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
With Sheets("TheSheetName")
If Application.CountA(.Range("A1:A5")) < 3 Then
MsgBox "All the cells in the range A1:A5 must be filled in.",
16, "Blank Cells"
Cancel = True
End If
End With
End Sub

If the pertinent cells are not contiguous, use the following:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
With Sheets("TheSheetName")
If Application.CountA(.Range("A1,B2,C3")) < 3 Then
MsgBox "All the cells in the range A1, B2, C3 must be filled
in.", 16, "Blank Cells"
Cancel = True
End If
End With
End Sub

Note that the 5 in the first macro and the 3 in the second macro represent
the number of cells in question.
Note that each of the above macros (you will use only one of them) is a
workbook event macro. This means it must be placed in the workbook module.
To do this, Right-Click on the icon to the left of the word "File" in the
menu across the top of your worksheet, select View Code and paste this macro
into that module. Click on the "X" at the top right corner of the module to
return to your spreadsheet. HTH Otto
"kcdonaldson" wrote in message
...
you hit it on the button. i'm sorry i should have said it more clearly a
macr
is exactly what i'm looking for . I want it to do exactly what what you
said.

"Otto Moehrbach" wrote:

What do you want to happen if some or all of those cells are not filled
in?
A formula in some cell can only return a value and only to that cell. It
cannot produce or create any action to entice or force the user to fill
in
those cells. That would require VBA.
A small VBA macro can be added to your file that would check for
entries
in those cells when an attempt is made to save or close the file. That
same
macro can display a message box telling the user whatever you want. That
same macro can also cancel the save/close command if the proper
conditions
are not met. Post back and provide more info about what you want to
happen
and when. HTH Otto

"kcdonaldson" wrote in message
...
I am trying to set up a department checklist that has to be filled out
before
the end of their shift. I would like to create a formula that would
verify
that certain cells have a value before the workbook closes. Can anyone
help
me with this one?