Workbook_Open Event 1

[pubert]

I am opening an Excel Workbook via VBA and the workbook I am opening has Workbook_Open Event. How do I bypass the Workbook_Open event in code when opening the workbook in code?

 

[msmith]

The simplest way to do this is to move your code or procedure calls out of the Workbook_Open event handler and put it into an Autpen sub. This is still available for backwards compatibility. It will fire upon normal workbook open, just like Workbook_Open but will not run when you open the workbook through VBA code in another workbook. If you needed to run the routine under these circumstances, howver, you would use something like the following:

Sub OpenSecondaryWkb()
Dim Wkb As Workbook
Dim Path as String

   Path = ThisWorkbook.Path & "\"  'for example
   Set Wkb = Workbooks.Open(Path & "Workbook_To_Open.xls")
   [b]Wkb.RunAutoMacros xlAutoOpen[/b]
   '...
   Set Wkb = Nothing
   
End Sub

p.s. Autpen must be in a standard code module.


Regards,
Mike

 

[AliThePro]

Just don't catch the event and you will bypass it. I think I might be missing the question. You don't have to catch every fired event.

 

[msmith]

AliThePro,

The problem as I understand it: Workbook #1 contains code in its Workbook_Open event handler that should run when this workbook is opened from the Excel interface but not when it is opened via code in Workbook #2.

My recommendation solves that issue because unlike the Workbook_Open event handler, the special module procedure Autpen (a throwback to versions of Excel prior to 97) will not automatically fire in a workbook opened via code.

However, you got me thinking. There is another, perhaps better, way to achieve the same thing. Assume the original setup (code or procedure calls in the Workbook_Open event handler of Workbook #1). Here is a revised version of my previous procedure:

Sub OpenSecondaryWkb()
Dim Wkb As Workbook
Dim Path as String

   On Error Resume Next
   Path = ThisWorkbook.Path & "\"  'for example
   [highlight]Application.EnableEvents = False[/highlight]
   Set Wkb = Workbooks.Open(Path & "Workbook_To_Open.xls")
   [highlight]Application.EnableEvents = True[/highlight]
   '...
   Set Wkb = Nothing
   
End Sub

Note the error handling. If this procedure errors without setting EnableEvents back to True, no other event handlers will fire.

Regards,
Mike