Auto_open functionality

[brindle1121]

I keep reading that auto_open is not a preferred, modern method and that it is retained for backwards compatibility purposes only. But auto_open is the only way I've found (so far) to trigger an XL event when a module opens in my application.

I have an application where the event detection trigger needs to be (seemingly) placed in a module. This is because the module's code is copied into the new workbook from a xxx.bas file located elsewhere. The IMPORT instruction.

application.vbe.activevbproject.vbcomponents.import c:\....

copies the file into the new module properly. (I have not found a way to copy code from an external source into a workbook or worksheet specific module).

When the new workbook opens, only the auto_open code creates an event. Workbook_open and Spreadsheet_open do not function in a module.

Is there another way to do this?

 

[nirvana_]

I may be wrong but is this what you are looking for.

1) Open New Excel File. Press F11 to goto VB Editor.
2) Select ThisWorkbook. In the drop downs select the event to - Workbook - Open. Type the code given below.
3) Make sure that the file c:\temp\script.bas exist which you want to automatically import when this file is opened.
4) Save the file and close it.
5) Try opening it again. The module script.bas should be automatically imported.

Private Sub Workbook_Open()
    Application.VBE.activevbproject.VBComponents.Import "c:\temp\script.bas"
End Sub

 

[Smitty]

Welcome to the Board!

The Workbook_Open event needs to go in the ThisWorkbook module, not a general module.

HTH,