Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need someone to help tweak a code | Excel Discussion (Misc queries) | |||
SUMPRODUCT - Tweak | Excel Worksheet Functions | |||
Does anyone know how to tweak Custom Auto Filter box? | New Users to Excel | |||
Array Help Tweak | Excel Worksheet Functions | |||
Can someone please tweak my Macro? | Excel Discussion (Misc queries) |