View Single Post
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
kcdonaldson
 
Posts: n/a
Default checking that cells have a value before the workbook will clos

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.

"Otto Moehrbach" wrote:

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