Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't it work?
I have this code in ThisWorkbook (it has to be there). But
I can't figure why it doesn't work. Can someone help? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim WS As Worksheet Dim WSs As Variant Dim Ndx As Long WSs = Array("Sheet1", "Sheet2") For Ndx = LBound(WSs) To UBound(WSs) Set WS = Worksheets(WSs(Ndx)) With WS If .[M15] < .[M16] Then MsgBox "Target cannot be greater than Chart Max" Cancel = True End If End With Next Ndx |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't it work?
What do you mean by doesn't work?
is M16 M15 in either Sheet1 or Sheet2 Maybe you want something like this: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim WS As Worksheet Dim WSs As Variant Dim Ndx As Long WSs = Array("Sheet1", "Sheet2") For Ndx = LBound(WSs) To UBound(WSs) Set WS = Worksheets(WSs(Ndx)) With WS If .[M15] < .[M16] Then MsgBox "Target cannot be greater than Chart Max" Cancel = True Application.Goto WS.[M16] Exit Sub End If End With Next Ndx End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... I have this code in ThisWorkbook (it has to be there). But I can't figure why it doesn't work. Can someone help? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim WS As Worksheet Dim WSs As Variant Dim Ndx As Long WSs = Array("Sheet1", "Sheet2") For Ndx = LBound(WSs) To UBound(WSs) Set WS = Worksheets(WSs(Ndx)) With WS If .[M15] < .[M16] Then MsgBox "Target cannot be greater than Chart Max" Cancel = True End If End With Next Ndx |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't it work?
Hi Tom, Thanks for your reply. In Sheet1, if I enter 100
in M15, and 120 in M16 I should get the message, and not be allowed to leave the wroksheet until M15 is greater than M16. Neither of these things happen. I copy/pasted your version (commented out my code) - still nothing happening. Could something elsewhere in the workbook be the problem? I opened a new workbook to test this code and can't get it working. Seems it should be possible, though... Thanks, Phil -----Original Message----- What do you mean by doesn't work? is M16 M15 in either Sheet1 or Sheet2 Maybe you want something like this: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim WS As Worksheet Dim WSs As Variant Dim Ndx As Long WSs = Array("Sheet1", "Sheet2") For Ndx = LBound(WSs) To UBound(WSs) Set WS = Worksheets(WSs(Ndx)) With WS If .[M15] < .[M16] Then MsgBox "Target cannot be greater than Chart Max" Cancel = True Application.Goto WS.[M16] Exit Sub End If End With Next Ndx End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... I have this code in ThisWorkbook (it has to be there). But I can't figure why it doesn't work. Can someone help? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim WS As Worksheet Dim WSs As Variant Dim Ndx As Long WSs = Array("Sheet1", "Sheet2") For Ndx = LBound(WSs) To UBound(WSs) Set WS = Worksheets(WSs(Ndx)) With WS If .[M15] < .[M16] Then MsgBox "Target cannot be greater than Chart Max" Cancel = True End If End With Next Ndx . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why doesn't it work?
As Chip said, it doesn't prevent one from leaving the worksheet - it
prevents one from closing the workbook with invalid conditions. It is triggered when you try to close the workbook, not when you select another sheet. The code must be in the thisworkbook module. The code works exactly as expected/as written for me. -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... Hi Tom, Thanks for your reply. In Sheet1, if I enter 100 in M15, and 120 in M16 I should get the message, and not be allowed to leave the wroksheet until M15 is greater than M16. Neither of these things happen. I copy/pasted your version (commented out my code) - still nothing happening. Could something elsewhere in the workbook be the problem? I opened a new workbook to test this code and can't get it working. Seems it should be possible, though... Thanks, Phil -----Original Message----- What do you mean by doesn't work? is M16 M15 in either Sheet1 or Sheet2 Maybe you want something like this: Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim WS As Worksheet Dim WSs As Variant Dim Ndx As Long WSs = Array("Sheet1", "Sheet2") For Ndx = LBound(WSs) To UBound(WSs) Set WS = Worksheets(WSs(Ndx)) With WS If .[M15] < .[M16] Then MsgBox "Target cannot be greater than Chart Max" Cancel = True Application.Goto WS.[M16] Exit Sub End If End With Next Ndx End Sub -- Regards, Tom Ogilvy "Phil Hageman" wrote in message ... I have this code in ThisWorkbook (it has to be there). But I can't figure why it doesn't work. Can someone help? Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim WS As Worksheet Dim WSs As Variant Dim Ndx As Long WSs = Array("Sheet1", "Sheet2") For Ndx = LBound(WSs) To UBound(WSs) Set WS = Worksheets(WSs(Ndx)) With WS If .[M15] < .[M16] Then MsgBox "Target cannot be greater than Chart Max" Cancel = True End If End With Next Ndx . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to update a column in a work based on another work sheet | New Users to Excel | |||
how can i automatically generate work order numbers from work orde | Excel Discussion (Misc queries) | |||
flash object dont work in my excel work sheet | Excel Discussion (Misc queries) | |||
Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
Is there away to keep "auto save" from jumping to the first work sheet in the work book? | New Users to Excel |