checking that cells have a value before the workbook will close

[kcdonaldson]

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?

 

[Otto Moehrbach]

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]

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]

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]

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]

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]

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]

KC
Here it is. The first macro below must be placed in the workbook
module. The second macro must be placed in a standard module. Note the
Public declaration above the second macro. That declaration must be at the
top of the standard module.
Watch out for line wrap in this message. For instance, in the line (in the
second macro) that says "For.......Array(....)", there are 6 strings in that
array and it must all be on one line.
This does what you said you wanted. Note the six strings "DesignationX" in
the second macro, where "X" is 1-6. I wrote the code this way to allow you
to substitute how you want to refer to each range in the message box that
displays if all the conditions are not met.
This can a bit confusing (thinking of line wrap). If you wish, email me
with a valid email address for you and I will send you the small file that I
used to develop this. In that file you will see all the code as it should
be and where it should be placed. My email address is
(e-mail address removed). Remove the "nop" from this address. HTH Otto
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Call ChkData
If CancelA = True Then Cancel = True
End Sub

Public CancelA As Boolean
Sub ChkData()
Dim RngName As Variant
Dim Msg As String
Dim Designation As String
CancelA = False
For Each RngName In Array("EOSV1", "SEMErrorsV1", "DEVLogOlivetteV1",
"DEVLogOverlandV1", "NMXNSGV1", "NMXSIMULTRANSV1")
If RngName = "EOSV1" Or RngName = "SEMErrorsV1" Then
If Application.CountA(Range(RngName)) < Range(RngName).Count
Then _
GoTo ErrorInData
Else
If Application.CountA(Range(RngName)) < 1 Then _
GoTo ErrorInData
End If
Next RngName
Exit Sub
ErrorInData:
CancelA = True
Select Case RngName
Case "EOSV1": Designation = "Designation1"
Case "SEMErrorsV1": Designation = "Designation2"
Case "DEVLogOlivetteV1": Designation = "Designation3"
Case "DEVLogOverlandV1": Designation = "Designation4"
Case "NMXNSGV1": Designation = "Designation5"
Case "NMXSIMULTRANSV1": Designation = "Designation6"
End Select
If RngName = "EOSV1" Or RngName = "SEMErrorsV1" Then
Msg = "All the cells in '" & Designation & "' must be filled in."
Else
Msg = "At least one of the cells in '" & Designation & "' must be
filled in."
End If
MsgBox Msg, 16, "Data Error"
End Sub

 

[kcdonaldson]

Ok, i used what you gave me and it works perfectly! I have one problem, how
can i save a blank master form that has no data in it and requires the
employee to fill it out? I guess basically bypassing this last macro to save
a blank master form. Also, I would like to have it so they have to enable the
macros or they will not be able to open the worksheet. Unless they have a
password or something so i can edit the workbook if need be.