Auto Event When New or Existing Workbook Opens

[JonS]

Hello,

Is there a way to trigger a macro every time a workbook is opened or a
new one is created?

This must not depend on the new workbook having the macro within it.
So it might be that such a macro is contained in Personal.xls or
xlstart workbook. Thanks,

JonS

[Chip Pearson]

Jon,

You need to use application level events, specifically the
NewWorkbook event. Put the following code in a class module, and
name the class CAppEvents.

Public WithEvents APP As Excel.Application
Private Sub Class_Initialize()
Set APP = Application
End Sub

Private Sub APP_NewWorkbook(ByVal Wb As Workbook)
' your code here
End Sub

Then, in a standard module, use

Dim AppEvents As CAppEvents
Set AppEvents = New CAppEvents

See http://www.cpearson.com/excel/appevent.htm for more
information.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com ch...@cpearson.com

"JonS" <jon.stan...@mail.com> wrote in message
news:136e2ea6.03041...@posting.google.com...

[JonS]

Chip,

Thanks you for your excellent reply.

I had some difficulty in getting the code to work. I then referred to
the link on your site and downloaded the spreadsheet example. Again I
found this an excellent example.

I found that the code above worked when I placed the non-class code
into the workbook code area. It did not work when placed (and macro
initiated) when it was in an ordinary module.

Thanks again for your help. Appreciated,

JonS