Chip, Entered the code - error: on the Set WS = Worksheets
(WSs(Ndx)) line: <Subscript out of range


-----Original Message-----
Phil,

Try something like the following. Just put your worksheet
name in
the Array function.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim WS As Worksheet
Dim WSs As Variant
Dim Ndx As Long

WSs = Array("Sheet1", "Sheet2", "Sheet3") '<<<<
Worksheets here
For Ndx = LBound(WSs) To UBound(WSs)
Set WS = Worksheets(WSs(Ndx))
With WS
If .[M15] < .[M16] Or .[M47] < .[M48] Or .[M79] < .
[M80] Then
MsgBox "Target cannot be greater than Chart Max"
Cancel = True
ElseIf .[M16] < .[M18] Or .[M48] < .[M50] Or .[M80]
< .[M82]
Then
MsgBox "UCL cannot be greater than Target"
Cancel = True
ElseIf .[M18] < .[M22] Or .[M50] < .[M54] Or .[M82]
< .[M86]
Then
MsgBox "LCL cannot be greater than UCL"
Cancel = True
End If
End With
Next Ndx

End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Phil Hageman" wrote in message
...
Chip, I have four worksheets with exactly the same
situation. Could you show me how to address this for
all
four worksheets - still in the workbook module?

Thanks, Phil
-----Original Message-----
Phil,

Put the following code in your ThisWorkbook code
module.
Change
the sheet name from 'SheetName' to the name of the
sheet
containing the cells you are checking. Note the
periods
before
each range. They are required.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Worksheets("SheetName")
If .[M15] < .[M16] Or .[M47] < .[M48] Or .[M79] < .
[M80]
Then
MsgBox "Target cannot be greater than Chart Max"
Cancel = True
ElseIf .[M16] < .[M18] Or .[M48] < .[M50] Or .[M80] < .
[M82] Then
MsgBox "UCL cannot be greater than Target"
Cancel = True
ElseIf .[M18] < .[M22] Or .[M50] < .[M54] Or .[M82] < .
[M86] Then
MsgBox "LCL cannot be greater than UCL"
Cancel = True
End If
End With
End Sub



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Phil Hageman" wrote in message
...
This worksheet code works okay - except that it
allows
the
user to leave the worksheet without correcting the
improper cell data entries. They can click okay to
the
message, and leave the worksheet. What additional
code
could be added to make the user correct the data
before
allowing them to leave the worksheet?

Private Sub Worksheet_Change(ByVal Target As Range)

If [M15] < [M16] Or [M47] < [M48] Or [M79] < [M80]
Then
MsgBox "Target cannot be greater than Chart Max"
ElseIf [M16] < [M18] Or [M48] < [M50] Or [M80] <
[M82]
Then
MsgBox "UCL cannot be greater than Target"
ElseIf [M18] < [M22] Or [M50] < [M54] Or [M82] <
[M86]
Then
MsgBox "LCL cannot be greater than UCL"
End If
End Sub

Thanks, Phil


.



.