Why doesn't it work?

[Phil Hageman]

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

 

[Tom Ogilvy]

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

 

[Phil Hageman]

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

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

.

 

[Tom Ogilvy]

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

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

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

.