For the EOS sheet - EOSV1 - this will check all cells in this range for data
in each cell.
For the SEM Errors sheet - SEMErrorsV1 - This will check all cells in this
group for data in each cell.
For the DEV Log sheet - DEVLogOlivetteV1 - this will check that there is
data in at least one cell in this group.
For the DEV Log sheet - DEVLogOverlandV1 - this will check that there is
data in at least one cell in this group.
For the NMX Alarms sheet - NMXNSGV1 - this will check that there is data in
at least one cell in this group.
For the NMX Alarms sheet - NMXSIMULTRANSV1 - this will check that there is
data in at least one cell in this group.
There isn't anything in particular that needsa to be in these cells, just
that they are not blank. As for the message i guess i would put a reference
in each message telling the user which range the blank cell is in. However, i
would give the user a different name than the actual range name i used in the
macro if possible. I really appreciate the help on this. This is a lifsaver!

"Otto Moehrbach" wrote:

Given what you said last, here is the methodology you need to be using
to make all you want happen. This is not a solution, this is just how we
are going to get there.
Since you have a bunch of cells, not just a handful, in each sheet to
check before saving the file, rather than list the cell addresses in the
macro statements, as you have done, we should use range names. Here is what
I mean. Let's work with sheet EOS to demonstrate what you should do. You
would then do the same with each of the other sheets and with each of the
other ranges on each sheet that you want to work with individually. In
other words, where you say you want to check for at least one cell filled in
each of two groups of cells one sheet, you need to apply a range name to
each of those two groups of cells. Select range names that are descriptive
(to you) of what they are in your file. For instance, for the range of
cells in sheet EOS, you might name the range EOS. Or anything else that
makes sense to you. A range name must start with a letter and must not have
any symbols or blank spaces in it. Like EOS1234 or DOODLE5. A range name
can be all letters if you wish.
To show you how to assign a range name, I'll use EOS1234 as the range
name. Here is what you do.
Select the EOS sheet.
Select cell B7 (the first cell in your listing).
Hold down the Ctrl key on your keyboard and don't let it go.
Select (click on) each of the remaining cells in your listing for sheet EOS,
until you have selected all 63 cells.
Release the Ctrl key.
Click on Insert - Name - Define in the menu across the top of your
spreadsheet.
Type in the range name you want to use for this range (EOS1234 in this
case).
Now do this for each sheet and each range within each sheet.
Now post back and tell me the list of range names you have just assigned.
Also tell me what you want Excel to check in each range you have just named.
You might also include what you want the pertinent message boxes to say if
the conditions for each range are not met. I assume that you would want a
message that is peculiar to each range so that the user would know where to
go to correct his error.
Be patient. Everyone who knows how to write macros didn't know how to write
macros before they learned how.
HTH Otto

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