I will try to go with something like this:

for documentation and some explanation on Application Level Events, you
might want to read Chip Pearson's site
http://www.cpearson.com/excel/appevent.htm

this reflects the method presented in VBA help, but see this simpler, more
logical method suggested by KeepitCool and Further endorsed by Jamie Collins
and which uses only the Thisworkbook class module (eliminating the need to
instantiate the class and which keeps everything in one place)

[KeepitCool, otherwise known as Jurgen Volkerink @ www.XLsupport.com ]

Some discussion:
http://groups.google.co.uk/groups?se....microsoft.com
the thread:
http://groups.google.co.uk/groups?th....microsoft.com

Source of article below:
http://groups.google.co.uk/groups?se....microsoft.com

From: keepITcool )
Subject: Using Events with the Application Object & XL 2003


View this article only
Newsgroups: microsoft.public.excel.programming
Date: 2005-01-23 18:47:05 PST



What Chip doesn't mention and what I find a lot easier...

you dont need a separate class module..
Thisworkbook IS a class module so you can keep all your code
(and the withevents application variable) there.

'thisworkbook code module...
Option Explicit

Private WithEvents XlsEvents As Application

Private Sub Workbook_Open()
Set XlsEvents = Application
End Sub

Private Sub XlsEvents_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "You just opened " & Wb.Name
End Sub

--
Regards,
Tom Ogilvy



"Jamie Collins" wrote in message
ups.com...

keepITcool wrote:
Tom,

You keep referring to Chip's page.

My preferred method is to use ThisWorkbook's codemodule for this and
dispense with the class alltogether (as you may know).

I never had any problems, but are there any advantages to using a
separate class module (Chip's method), that I'm not aware of?
I just can't see the point.

Tom,
You keep referring to Chip's page

It's the established way of doing things <g. It takes a lot to work to
successfully challenge deeply held views.

FWIW I agree with you that ThisWorkbook is a fine place to host the
WithEvents Excel.Application object and it too is my preferred
approach. One of the regulars accused me of having bad taste for
suggesting this so seems an emotive subject. IIRC Chip reserves the
ThisWorkbook code module solely for event handlers of the Workbook
events, so at least he is consistent with his methodologies <g.

Personally, I use ThisWorkbook for all properties and methods that need
to be available globally to the workbook (and I'm not talking Public, I
mean Friends). Again, this is not the establishment's way; the crowd
prefer standard modules.

It is my opinion that using a class module to host the WithEvents
Excel.Application object is a bit counterintuitive. Is it encapsulation
for the sake of it? How many instances of this EventClass will there be
in a single project? Always and only one, I fancy. Let's face it: the
only reason it's in a class module at is because the WithEvents keyword
is only supported for class modules. If they could get away with
putting it in a standard module, they would <g.

For many people, this EventClass is their first and only class module
but it isn't really a 'proper' class. It is my opinion that such
'single use' classes can hinder newbies learning the joys of classes,
OOP and all that in VBA.

Don't get me wrong, I'm not antiestablishment (well, maybe a little
<g). Tom and Chip (and others) have helped me grasp the basics but I
never stopped thinking and discovering things for myself. Tom is kind
enough to frequently link to/re-post my code and even Chip has
previously revised his aforementioned page on EventClass after I gave
him some feedback. Yes, they do listen to the likes of us.

As I said, I agree with you but it will take a lot of effort to
challenge this established approach.

Jamie.

--