Hello:

I have a auto_close routine in my VBA project that
initiates when the user tries to close the file either
intentionaly or by accident.

If the closure is accidental I would like to cancel file
closure. Is there an easy way to do this?

here is the current code:

Sub auto_close()

'Declare variables
Dim intResponse As Integer
Dim strDatestamp, strPath As String

'Check to confirm file exit
intResponse = MsgBox("Are you sure you wish to Exit?",
vbYesNo)

If intResponse = 6 Then
ThisWorkbook.SaveAs Filename:=ABC.xls
else
'?????? CANCEL CLOSURE]
end if
end sub

Your help is appreciated.

Thanks,
-Neil

Neil,

Use Workbook BeforeClose

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Declare variables
Dim intResponse As Integer
Dim strDatestamp, strPath As String

'Check to confirm file exit
intResponse = MsgBox("Are you sure you wish to Exit?", vbYesNo)

If intResponse = vbYes Then
ThisWorkbook.SaveAs Filename:=ABC.xls
Else
Cancel = True
End If

End Sub

Put it in ThisWorkbook code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Neil" wrote in message
...
Hello:

I have a auto_close routine in my VBA project that
initiates when the user tries to close the file either
intentionaly or by accident.

If the closure is accidental I would like to cancel file
closure. Is there an easy way to do this?

here is the current code:

Sub auto_close()

'Declare variables
Dim intResponse As Integer
Dim strDatestamp, strPath As String

'Check to confirm file exit
intResponse = MsgBox("Are you sure you wish to Exit?",
vbYesNo)

If intResponse = 6 Then
ThisWorkbook.SaveAs Filename:=ABC.xls
else
'?????? CANCEL CLOSURE]
end if
end sub

Your help is appreciated.

Thanks,
-Neil

Correct me if I'm wrong about this, but I thought that by
the time Auto_Close runs, the workbook is already closed or
is in the process of closing.
-----Original Message-----
Hello:

I have a auto_close routine in my VBA project that
initiates when the user tries to close the file either
intentionaly or by accident.

If the closure is accidental I would like to cancel file
closure. Is there an easy way to do this?

here is the current code:

Sub auto_close()

'Declare variables
Dim intResponse As Integer
Dim strDatestamp, strPath As String

'Check to confirm file exit
intResponse = MsgBox("Are you sure you wish to Exit?",
vbYesNo)

If intResponse = 6 Then
ThisWorkbook.SaveAs Filename:=ABC.xls
else
'?????? CANCEL CLOSURE]
end if
end sub

Your help is appreciated.

Thanks,
-Neil

.

Instead of an Auto_Close routine, put your code in the Workbook_BeforeClose
event code in the Thisworkbook module. Workbook_BeforeClose has an argument,
Cancel. If you set it to TRUE, the closure will be cancelled.


On Wed, 7 Jul 2004 11:59:39 -0700, "Neil" wrote:

Hello:

I have a auto_close routine in my VBA project that
initiates when the user tries to close the file either
intentionaly or by accident.

If the closure is accidental I would like to cancel file
closure. Is there an easy way to do this?

here is the current code:

Sub auto_close()

'Declare variables
Dim intResponse As Integer
Dim strDatestamp, strPath As String

'Check to confirm file exit
intResponse = MsgBox("Are you sure you wish to Exit?",
vbYesNo)

If intResponse = 6 Then
ThisWorkbook.SaveAs Filename:=ABC.xls
else
'?????? CANCEL CLOSURE]
end if
end sub

Your help is appreciated.

Thanks,
-Neil