Closing application only thru VBA

Question

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!

Comintern · Accepted Answer · 2017-03-03 05:07:54Z

2

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...

answered Mar 3, 2017 at 5:07

Comintern

22.3k6 gold badges36 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

steegness Over a year ago

Oh hey, what I was typing, but with examples! Nice. :)

Alex Over a year ago

Working like a charm! Thank you!

steegness · Accepted Answer · 2017-03-03 05:08:55Z

-1

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?

answered Mar 3, 2017 at 5:08

steegness

4591 gold badge4 silver badges10 bronze badges

Collectives™ on Stack Overflow

Closing application only thru VBA

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related