CODEWITHSUNDEEP
excelvbaexit
Alex
Alex

Reputation: 108

Closing application only thru VBA

I have this workbook and I want to make it look like a program. I also want people using it to only be able to quit the application thru a specific command button.

Here is the code I have

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "{ESC}"
Cancel = True
MsgBox "Please use the QUIT button to save and close the database."
End Sub

And for the quit button:

Sub SAVE_CLOSE
ActiveWorkbook.Save
If Workbooks.Count < 2 Then
Application.Quit
Else
ActiveWorkbook.Close
End If
End Sub

My problem is that when the user quit the application thru this button, it triggers the Private Sub Workbook_BeforeClose event and cancel the quitting process. How would you fix this?

Thanks!

Upvotes: 1

Views: 256

Answers (2)

steegness
steegness

Reputation: 459

Since Workbook_BeforeClose() is called whenever the workbook is about to close (whether it's your method or not) you'll need some way within that method to know that it was your button that called it. Perhaps a global variable, or a value in a given cell on a hidden sheet?

Upvotes: -1

Comintern
Comintern

Reputation: 22185

Just set a flag in the button handler and test for it in the BeforeClose event:

In ThisWorkbook...

Public okToClose As Boolean                      '<--module level.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not okToClose Then
        Application.OnKey "{ESC}"
        Cancel = True
        MsgBox "Please use the QUIT button to save and close the database."
    End If
End Sub

...and in the handler...

Sub SAVE_CLOSE()
    ActiveWorkbook.Save
    ThisWorkbook.okToClose = True
    If Workbooks.Count < 2 Then
        Application.Quit
    Else
        ActiveWorkbook.Close
    End If
End Sub

Note that you should avoid calling ActiveWorkbook.Close - use a hard reference instead. That's a really good way to destroy somebody's day's worth of work...

Upvotes: 2

Related Questions