Application.Quit in Excel 2007

[Paul D.]

I want to force users to close the Excel Application using a CloseButton on a
UserForm rather than the X button, so I have the following code:

In the ThisWorkbook Module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If CloseMode = vbFormControlMenu Then
Cancel = True
Open_Switchboard 'name of UserForm
End If
End Sub

In the Userform:

Private Sub cmdCloseButton_Click()
Application.Quit
End Sub

When I click the CloseButton on the UserForm, nothing happens. If I remove
the code from "Private Sub Workbook_BeforeClose(Cancel As Boolean)" then the
application quits when I click the CloseButton.

Why is that? Is this a bug in Excel 2007? Any help?

 

[Paul D.]

This application is saved in "Compatibility Mode" if that makes any difference.
I searched Excel 2007 Help file for "CloseMode", nothing in help.

 

[NickHK]

Paul,
IIRC, CloseMode is only valid on a userform. It is not supported on the
Excel app - unless that has changed in XL2007.
This approach will not work.

Maybe setting a flag when the userform unload and call application.quit.
In the Workbook_BeforeClose, check the value of the flag and set
cancel=True/false.

NickHK

 

[Paul D.]

Thanks NickHK
I tried Public Ok2Close as Boolean at the module level.
In a procedure I wrote:
Ok2Close = True
Application.Quit

The first line in: Private Sub Workbook_BeforeClose(Cancel As Boolean)
Debug.Print Ok2Close

the value of Ok2Close is always False

The variable Ok2Close looses its set value between the Module and
ThisWorkbook. Can't figure out why.

 

[NickHK]

Paul,
It sounds like it's a question of SCOPE.

Declare the variable Public in a standard module, not on the worksheet
module, or call it with
Sheet2.Ok2Close = True

NickHK