Hi. You can trap the workbook's BeforeClose event to prevent users closing the workbook unless some condition of your choosing is set.
The event sub is placed not in a global code module but in the code behind the ThisWorkbook component of the VBA project. In the example I tested I cancelled the close event unless a global variable defined in a global code module was set True:
- Private Sub Workbook_BeforeClose(Cancel As Boolean)
-
If Not blCanClose Then
-
Cancel = True
-
Else
-
' Your Code to Advise User Here
-
End If
-
End Sub
In the global code module (inserted into the VBA project by selecting Insert, Module from the menu) I placed the following test code:
- Public blCanClose As Boolean
-
-
Public Sub CloseForm()
-
blCanClose = True
-
Application.Quit
-
End Sub
Finally, I placed a command button on the first worksheet and called CloseForm from its On-Click event:
- Private Sub CommandButton1_Click()
-
CloseForm
-
End Sub
Please note that you may wish to give more thought to providing some form of exit for users if they cannot click your custom exit button - as cancelling the BeforeClose event gives no other direct way out for your users.
-Stewart
PS Instead of cancelling the workbook closure, you could always use the BeforeClose event to run your own exit routine prior to closing - making sure that whatever you want to do is done every time, no matter which route out your users take!
5
