Thread
:
Need final code tweak
View Single Post
#
13
Posted to microsoft.public.excel.programming
Chip Pearson
external usenet poster
Posts: 7,247
Need final code tweak
Phil,
The code is for the BeforeClose event procedure, and will prevent
the user from closing the workbook. There is no way to prevent the
user from moving to another workbook as long as this workbook is
still open. You can move all the code to the Deactivate event,
less the Cancel = True statements, to display the message boxes,
but you can't prevent the user from merely switching to another
workbook.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Phil Hageman" wrote in message
...
Chip, It works!!! Thanks for all the help. One thing
remains though, I can still leave the worksheet with
improper data. Is the Cancel = True suppose to prevent
leaving the worksheet?
Phil
-----Original Message-----
Phil,
Check the spelling of the sheet names you entered in the
WSs =
Array(...) line. Make sure they are spelled right,
including
spaces. The code works fine as written if the spelling
of the
sheet names are correct.
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
"Phil Hageman" wrote in message
...
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
.
Reply With Quote
Chip Pearson
View Public Profile
Find all posts by Chip Pearson