Auto Event When New or Existing Workbook Opens

72 views
Skip to first unread message

JonS

unread,
Apr 14, 2003, 9:32:33 PM4/14/03
to
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

unread,
Apr 14, 2003, 9:44:46 PM4/14/03
to
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

unread,
Apr 16, 2003, 9:52:35 PM4/16/03
to
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

Reply all
Reply to author
Forward
0 new messages