Event in a add-in called verytime a file is opened

[Torben Laursen]

Hi

I have a add-in and I want it to run a check each time the user open a file
in Excel to see if it has a sheet called Project. If it does it has to run
some code.

But I cannot find the event to use, can anyone help me?

Thanks!
Torben Laursen

 

[Guest]

This is an application level event, never-the-less relatively simple.

with your XLA add a Class Module with this code

Option Explicit
Public WithEvents xl As Excel.Application
Private Sub Class_Initialize()
Set xl = Excel.Application
End Sub
Private Sub xl_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Book Opened"
''' call your code from here
End Sub

call the class cXL

You need a standard code module with code that inclused this...

Option Explicit
Public xl As cXL
Sub Auto_Open()
Set xl = New cXL
End Sub


now, when you XLA loads, its auto_open sub is called, which sets the class
cXL to the actice Ecel application./ Now, whenever a workbook is opened, the
cXL's class event will fire, raising the message....just replace this by a
call to whatever sub you want.

Demo file available

Patrick Molloy
Microsoft Excel MVP

 

[Torben Laursen]

Hi Patrick

Thanks for the code.
What do I need to do so I can use the Auto_Open function that you give.

If I have it in I get a error when opening a file for the first time:
"Ambiguous name detected: Auto_Open".
I have check the code and there is only one function called Auto_Open.

Is there some other way that I can run the code inside Auto_Open?

Thanks
Torben Laursen

 

[Dave Peterson]

I'd double check.

In the VBE, ctrl-f (but find in the project--not just a single module).