Need final code tweak
 

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

Need final code tweak
 

There's a worksheet_deactivate event that you could use. And under the
ThisWorkbook module, you might want some code in the workbook_beforeclose event,
too.



Phil Hageman wrote:

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

--

Dave Peterson

Need final code tweak
 

Phil
To add to Dave's response:. Instead of displaying to the user a message
box with an OK button (that he can click and leave the sheet), display a
message box with only a Yes and a No button. The message box would tell him
that such and such has to be corrected and ask him if he wants to correct
it. State that a No response will result in data such and such being
deleted. If he selects Yes, then delete the pertinent data and he gets to
try again. HTH Otto
"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

Need final code tweak
 

Dave, thanks much for your reply. Not being a programmer,
I thought there might be a line of code that could simply
be added to what I have that would prevent the user from
leaving the worksheet. Is there? Could you help me with
it?
Thanks, Phil

-----Original Message-----
There's a worksheet_deactivate event that you could use.
And under the
ThisWorkbook module, you might want some code in the
workbook_beforeclose event,
too.



Phil Hageman wrote:

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

--

Dave Peterson

.

Need final code tweak
 

Phil

1. why write code where simple data validation can do the same?

2. prevent the user leaving the sheet -

Private Sub Worksheet_Deactivate()
call worksheet_change([a1])
End Sub

3. REREAD point 1.. THAT'll solve it.


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Phil Hageman" wrote:

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

Need final code tweak
 

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

Need final code tweak
 

If i read the logic of your code/messages correctly

you check if [M15] is less than [M16]; if it is you say Target cannot be
greater than Chart max, so [M16] must be the target and [M15] must be the
chart max

you then check if [M16] is less than [M18]; if it is you say UCL cannot be
greater than Chart Max, so [M16] must be the chart max and [M18] must be the
UCL

you then check if [M18] is less than [M22]; if it is you say LCL cannot be
greater than UCL, so [M22] must be LCL and [M18] must be the UCL

[M15] - chart max
[M16] - both target and chart max
[M18] - UCL
[M22] - LCL

Maybe I am misinterpreting, but perhaps you need to take another look.


--
Regards,
Tom Ogilvy


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

Need final code tweak
 

Hi Tom. This is the required order of things:
Cell Name
M15 Chart Max
M16 Target (Other cell names in the IF
M18 UCL ElseIf lines are repeats of the
M22 LCL same situation in other locations
M26 Op Zero in the same worksheet.)
M29 Chart Min

Values must descend from M15 to M29. These values are
flowing into a matrix that creates a combination chart.
I'm trying to enforce this with the users. Since I have
40 cases of this workbook, I would like to place the code
in the workbook module for ease of implementation and
maintenance.

Any advice?
Thanks, Phil




-----Original Message-----
If i read the logic of your code/messages correctly

you check if [M15] is less than [M16]; if it is you say
Target cannot be
greater than Chart max, so [M16] must be the target and
[M15] must be the
chart max

you then check if [M16] is less than [M18]; if it is you
say UCL cannot be
greater than Chart Max, so [M16] must be the chart max
and [M18] must be the
UCL

you then check if [M18] is less than [M22]; if it is you
say LCL cannot be
greater than UCL, so [M22] must be LCL and [M18] must be
the UCL

[M15] - chart max
[M16] - both target and chart max
[M18] - UCL
[M22] - LCL

Maybe I am misinterpreting, but perhaps you need to take
another look.


--
Regards,
Tom Ogilvy


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


.

Need final code tweak
 

Phil,

Very simply adapting to Chip's code, just repeat for each sheet. Let's
assume the sheets are called Summary, Detail1, Detail2 and Work, then

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Worksheets("Summary")
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

With Worksheets("Detail1")
'repeat the code
End With

With Worksheets("Detail2")
'repeat the code
End With


With Worksheets("Work")
'repeat the code
End With


End Sub

--

HTH

Bob Phillips

"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


.

Need final code tweak
 

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


.



.

Need final code tweak
 

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

Need final code tweak
 

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



.

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



.