RUN VB / MACRO ON CLOSING A WORSHEET

[BazJ]

I have placed a Quit button on the worksheet in order that the user will reset a number of changes in the Excel enviroment (toolbars on / off). But occasionaly thet are still using the little x button at the top of the sheet / Excel.

Is there a way to:
a. Run a macro before closing the worksheet without a users intervention.
b. Disabling the x button. I know it can be done on a form but is it possible on a worksheet.

Many thanx

BazJ

 

[RSi2]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'your code here
End Sub


hope this will help

 

[ratman]

Consider modifying these options:

'these would go in the ThisWorkbook module.
'this prevents closing of a workbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Cancel = True
End Sub

'-----this prevents saveas of a workbook.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
Cancel = True
End If
End Sub

'----this sub would go in a new module----
'----workbook will close without saving.
Sub auto_close()
Application.DisplayAlerts = False
ActiveWorkbook.Close
End Sub Thank you,
Dave Rattigan