Need final code tweak

P

Phil Hageman

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
 
D

Dave Peterson

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 said:
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
Click to expand...
 
O

Otto Moehrbach

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
 
P

Phil Hageman

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 said:
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
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
K

keepitcool

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 >
 
C

Chip Pearson

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 (e-mail address removed)
 
T

Tom Ogilvy

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.
 
P

Phil Hageman

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 said:
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
Click to expand...


.
Click to expand...
 
B

Bob Phillips

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 said:
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 (e-mail address removed)


Phil Hageman said:
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
Click to expand...


.
Click to expand...
Click to expand...
 
P

Phil Hageman

Chip, Entered the code - error: on the Set WS = Worksheets
-----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 (e-mail address removed)




Phil Hageman said:
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
Click to expand...
module.
Change
the sheet name from 'SheetName' to the name of the sheet
containing the cells you are checking. Note the
Click to expand...
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] < .
Click to expand...
[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 (e-mail address removed)


This worksheet code works okay - except that it
Click to expand...
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] <
Click to expand...
[M82]
Then
MsgBox "UCL cannot be greater than Target"
ElseIf [M18] < [M22] Or [M50] < [M54] Or [M82] <
Click to expand...
[M86]
Then
MsgBox "LCL cannot be greater than UCL"
End If
End Sub

Thanks, Phil


.
Click to expand...
Click to expand...


.
Click to expand...
 
C

Chip Pearson

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 (e-mail address removed)


Phil Hageman said:
Chip, Entered the code - error: on the Set WS = Worksheets
-----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 (e-mail address removed)
Click to expand...
Click to expand...
 
P

Phil Hageman

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 (e-mail address removed)


Phil Hageman said:
Chip, Entered the code - error: on the Set WS = Worksheets
-----Original Message-----
Phil,

Try something like the following. Just put your
Click to expand...
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 (e-mail address removed)
Click to expand...
Click to expand...


.
Click to expand...
 
C

Chip Pearson

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 (e-mail address removed)


Phil Hageman said:
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 (e-mail address removed)


Phil Hageman said:
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 (e-mail address removed)
Click to expand...


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code for msg box 4
Coding for msg box 1
Consolidating module code 6
Good code not working??? 1
Why doesn't it work? 3
Enforced entries code 1
Need Formula Listing Code Modification 3
Breaking up a procedure into modules 3

Top