MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How to stop a user from closing a window


Posted by Mark G. on November 23, 2001 1:41 PM

Is there a setting in VBA that will not allow a user to close the window when they click the 'x'-out button?

Basically, they are entering data, and I want to force them to press the command button to continue, otherwise the macro will error out.


Posted by lenze on November 23, 2001 2:43 PM

Mark, you might try something like this in the Workbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If ActiveWorkbook.Saved = False Then
MsgBox "Please click the command button to continue"
Cancel = True
End If

End Sub

This will stop the workbook from closing without being saved when they click the "x". The macro, however, which you are running will need a line of code to set the saved property of the workbook to True before it can be clsoed.

Hope this helps

Posted by MarkG. on November 26, 2001 6:34 AM

Thanks, lenze for your help.

: Is there a setting in VBA that will not allow a user to close the window when they click the 'x'-out button?