Application level events - calling from standard module

[triaz]

I need to call an application event from a standard module, the
difference here is that it needs to be called each time a new workbook
is opened.

This has been developed as an addin so that any user can install the
application with little or no effort.

Initially the code was located in the standard module and any automatic
procedures called from ThisWorkbook's Workbook_open event. The code
basically builds a custom menu, amongst other things, which checks
certain conditions within a workbook and then disables part of the
custom menu should these conditions be true.

I need this code to be run whenever a workbook is opened, hence
application level events - my problem is that workbook_open events are
not available on the standard module as far as I know.

Am I going about this the right way or are there any alternatives?

I hope this is clear.

Thanks in advance

T.

 

[Guest]

This is just a shade trickier than you probably first imagined it would be.
You need to instantiate a class to catch the events... Create a class module
and Name it clsXLEvents. In it place the following code...

Option Explicit
Private WithEvents xlApp As Excel.Application

Private Sub Class_Initialize()
Set xlApp = Excel.Application
End Sub

Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Tada"
End Sub

Now in a standard module add the following code

Dim objXLEvents As clsXLEvents

Public Sub Auto_Open()
Set objXLEvents = New clsXLEvents
End Sub

This should do it for you...

 

[triaz]

Thanks Jim, you're a diamond. One very slight, not really that
important, issue. It does not seem to work when opening a blank / new
workbook?

Thank you.

T.