disable the X to close

[ajtilley]

Hi

Can anyone help, is there a VBA code which i can put into an excel 2007 workbook which disables the x button on the top right hand corner and forces the user to use a macro enabled button to close.

I ask because at present i have disabled save option on close, but i want to make sure people dont accidentally close the file without saving.

Thanks for any help.

 

[VoG]

In the ThisWorkbook module

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
End Sub

Then in your code to close the file you will need to include

Application.EnableEvents = False

to prevent the event code from running.

 

[ajtilley]

I put this code in, however it does not seem to make a difference, if a person presses the x to close, it still closes, naturally without saving. I cant see why it isnt working.
I have tried these variations of the workbook code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Cancel = "True"
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Me.Saved = "True"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
     Me.Saved = "True"
     Cancel = "True
End Sub

The Macro code for save and quit was as follows:

Sub saveclose()
'
' saveclose Macro
'
'
    Application.EnableEvents = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
End Sub

 

[VoG]

The code that I posted has to go in the ThisWorkbook module. In the VBE, in the Project window double click ThisWorkbook and paste in the code.

You should also turn events back on

Sub saveclose()
'
' saveclose Macro
'
'
    Application.EnableEvents = False
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    Application.EnableEvents = True
End Sub