problems closing workbook

[Kevin]

I'm not certain, but it appears I've crated a conflict between my Workbook-level code and the module-level code. When I close the workbook using the "X", the "Enable Macros" / "Disable Macros" window displays...after 10 seconds; the duration of the timer. What can I do to eliminate the Macros window

Thanks

Kevi

The following code exists in Module1
Sub Autpen(
Application.OnTime Now + TimeValue("00:00:10"), "Close_Workbook
End Su
Sub Close_Workbook(
Application.DisplayAlerts = Fals
Application.Qui
End Su

The following code exists in ThisWorkbook
Sub Workbook_BeforeClose(Cancel As Boolean
ThisWorkbook.Saved = Tru
ThisWorkbook.Clos
End Sub

 

[Bill Manville]

I'm not certain, but it appears I've crated a conflict between my Workbook-level code
and the module-level code.
When I close the workbook using the "X", the "Enable Macros" / "Disable Macros" window displays...after 10 seconds; the duration of the timer. What can I do to eliminate the Macros window?

The problem is that the OnTime event is not cancelled when you close the workbook
- only when you exit from Excel

Try this:

Module1:
Public ClosingTime As Date

Sub Autpen()
ClosingTime = Now + TimeValue("00:00:10")
Application.OnTime ClosingTime, "Close_Workbook"
End Sub
Sub Close_Workbook()
Application.DisplayAlerts = False
Application.Quit
End Sub

ThisWorkbook:
Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime ClosingTime, "Close_Workbook", schedule:=False
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup

 

[Kevin]

Thanks Bill. Works fine now.

----- Bill Manville wrote: -----

I'm not certain, but it appears I've crated a conflict between my Workbook-level code
and the module-level code.
When I close the workbook using the "X", the "Enable Macros" / "Disable Macros" window displays...after 10 seconds; the duration of the timer. What can I do to eliminate the Macros window?

The problem is that the OnTime event is not cancelled when you close the workbook
- only when you exit from Excel

Try this:

Module1:
Public ClosingTime As Date

Sub Autpen()
ClosingTime = Now + TimeValue("00:00:10")
Application.OnTime ClosingTime, "Close_Workbook"
End Sub
Sub Close_Workbook()
Application.DisplayAlerts = False
Application.Quit
End Sub

ThisWorkbook:
Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime ClosingTime, "Close_Workbook", schedule:=False
ThisWorkbook.Saved = True
ThisWorkbook.Close
End Sub


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - reply in newsgroup