Can You Re-Create this problem?
 

Take 2 minutes and try the following. Let me know if you can you re-create
this problem:

1) Put this code in the ThisWorkbook module of a new blank Excel Workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Worksheets("Sheet2").Activate
End Sub


2) Save the workbook as Test1.xls
3) Close Excel.
4) Make a copy of Test1.xls and name it Test2.xls.
5) Open both Test1.xls and Test2.xls.
6) Click the Close ("X") button for Excel (the upper right hand corner of
the Excel window).


You will notice that neither workbook closes. What is that all about?

Can You Re-Create this problem?
 

Hi
same problem with Excel 2003 8no idea why). But the following works for
me:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Worksheets("Tabelle2").Activate
Application.EnableEvents = False
Me.Close
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


John Camburn wrote:
Take 2 minutes and try the following. Let me know if you can you
re-create this problem:

1) Put this code in the ThisWorkbook module of a new blank Excel
Workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Worksheets("Sheet2").Activate
End Sub


2) Save the workbook as Test1.xls
3) Close Excel.
4) Make a copy of Test1.xls and name it Test2.xls.
5) Open both Test1.xls and Test2.xls.
6) Click the Close ("X") button for Excel (the upper right hand
corner of the Excel window).


You will notice that neither workbook closes. What is that all about?

Can You Re-Create this problem?
 

Thanks for confirming that I am not going insane!

I also appreciate the workaround. I had already tried wrapping the Activate
call with EnableEvents, but that didn't work. The Me.Close is a stroke of
simplistic genius. I still don't understand why it's necessary though.

Thanks again.

John


"Frank Kabel" wrote in message
...
Hi
same problem with Excel 2003 8no idea why). But the following works for
me:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Me.Worksheets("Tabelle2").Activate
Application.EnableEvents = False
Me.Close
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany


John Camburn wrote:
Take 2 minutes and try the following. Let me know if you can you
re-create this problem:

1) Put this code in the ThisWorkbook module of a new blank Excel
Workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Worksheets("Sheet2").Activate
End Sub


2) Save the workbook as Test1.xls
3) Close Excel.
4) Make a copy of Test1.xls and name it Test2.xls.
5) Open both Test1.xls and Test2.xls.
6) Click the Close ("X") button for Excel (the upper right hand
corner of the Excel window).


You will notice that neither workbook closes. What is that all about?