Excel shuts down when macros enabled due to Workbook_Open event

[PIMotion]

I have MSOffice 2007 installed with latest patches. Previously, even with
Excel 2000 on same machine this behavior occurred.

Situation: to isolate using the simplest of examples... a blank XLS (97-2003 format) sheet - no formulas or formatting, and no fancy VBA code. VBAProject code exists in just ThisWorkbook.Workbook_Open event. Even if just one lonely comment character exists within the Workbook_Open event, Excel shuts down when macros are enabled. I am using just the Excel installation, no subsequent add-ins or tweaking or templates modified, added, deleted, manipulated. Macro security is set to lowest. Excel option to trust is granted for VBA.

Example setup: Open a new workbook. Go to VBAProject. For the ThisWorkbook object, enter (or after choosing from drop-down):

Private Sub Workbook_Open()
'
End Sub

The comment character within the event could have been a Msgbox or some other code, but can't get much simpler or more harmless than a comment character! Having any non-blank character or characters in the Workbook_Open event causes Excel to shut down when macros are enabled.

However, if the event was empty of code, as follows:

Private Sub Workbook_Open()

End Sub

then no problem - Excel stays open. Doesn't matter if there are other macros within let's say Module1 or some module name. Only when non-blank character within Workbook_Open event will Excel shut down.

A solution is needed for an XLS (97-2003 format) file.

Cannot use Autpen subroutine within the VBAProject because the workbook is typically opened and manipulated externally from other applications (such as VB component), and the 'caller' will not be using the RunAutoMacros method.

Would appreciate suggestions regarding cause of problem and remedy. Thanks for consideration!

 

[ctcaccount]

This is a bit strange.

Well, few suggestions to be able to identify the problem. Use the following sub:

Private Sub Workbook_Open()
MsgBox "I'm opening"
End Sub

Also make a similar beforeClose Sub with a different message.

Now opening this file with Macros enabled will display massages whose sequance will tell you which event occurred first. This is a must to identify where to look for problems

 

[PIMotion]

Thanks for your review and response. Good suggestion about the beforeclose event. Here is the code I inserted into the 'blank' workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "before close. Cancel =" & Cancel
End Sub

Private Sub Workbook_Open()
MsgBox "in Workbook_Open event"
End Sub

Workbook opened. Macro Security was set to disable with notification. When I 'enabled this content', neither event fired (no msgs executed). Excel shut down with error. Application event log indicates faulting module unknown. Here is the log entry:

Faulting application excel.exe, version 12.0.4518.1014, stamp 45428263, faulting module unknown, version 0.0.0.0, stamp 00000000, debug? 0, fault address 0x0020566c.

 

[ctcaccount]

One thing you might want to check is the Autpen event in the "Thisworkbook" code. This is equivalent to the workbook_open event.

If you did not find any error source in there, and considering the errors mentioned, I suggest that you re install the office program.

Thanks for your review and response. Good suggestion about the beforeclose event. Here is the code I inserted into the 'blank' workbook.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "before close. Cancel =" & Cancel
End Sub

Private Sub Workbook_Open()
MsgBox "in Workbook_Open event"
End Sub

Workbook opened. Macro Security was set to disable with notification. When I 'enabled this content', neither event fired (no msgs executed). Excel shut down with error. Application event log indicates faulting module unknown. Here is the log entry:

Faulting application excel.exe, version 12.0.4518.1014, stamp 45428263, faulting module unknown, version 0.0.0.0, stamp 00000000, debug? 0, fault address 0x0020566c.

 

[PIMotion]

Thanks for your continued support of this issue. The Autpen executes automatically only when Excel is launched manually. The Autpen is not executed automatically by Excel when the application and subsequently target workbook is opened via code (such as via VB). The 'caller' code would need to explicitly instruct Excel to execute the Autpen macro using object.RunAutoMacros xlAutoOpen. Unfortunately that is not a workable solution because the target workbook is typically accessed and processed by 'caller' code (i.e., VB app), and due to development restriction the caller cannot be modified (to use the RunAutoMacros method).

There are definitely no source errors in the workbook. Testing the behavior using a 'blank' workbook with only the Workbook_Open event code in the VBAProject. Even having just a comment character within the event (and no other code, such as Msgbox, etc.) causes Excel to shut down when macros are enabled.

Already have uninstalled, scrubbed registry, and re-installed MSO 2007. Same problem behavior is observed.

Any other suggestions welcome. Thanks again for taking the time to offer assistance.

 

[PIMotion]

Turns out this problem behavior was resolved when the afflicted server's OU policy was updated to exclude Excel. Or something along those lines. Case closed!

 

[hofman]

Turns out this problem behavior was resolved when the afflicted server's OU policy was updated to exclude Excel. Or something along those lines. Case closed!

Have you got more information. I have the same problem with Excel. Thank you.