autoexecute macros on open or new

[Tony]

How to execute macros on File / New or File / Open in
Excel XP ?

So far I have found that if I will put the procedure named
Workbook_Open in ThisWorkbook it works. But I would like
to have two different actions executed on Open and on New.
Should I name them Workbook_Open and Workbook_New and have
them stored in ThisWorkbook ? Or is there any way to have
them as a modules with some specific names ?

Regards,

Tony

 

[Bob Phillips]

Tony,

There is no Workbook_New event, so you have a couple of choices.

Either, using application events to trap the App newworkbook event.
Problems, a bit more complex, and will happen for every new workbook.

Or, if it is only to happen for new workbooks from a particular template,
add workbook open code to that template.

The code can be put in more meaningfully named macros, and just call those
macros from the event code.

 

[halem2]

how about if you change the personal.xls and add whatever commands yo
need to the Auto_Open?

That would work

 

[Dave Peterson]

You could use an application event to look for opening an existing workbook or
creating a new workbook.

Create a new workbook.
hit alt-f11
find your workbook/project and rightclick on it.
Insert|class module
It should be called Class1 (default name).

Paste this in that code window:


Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
MsgBox "New workbook"
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Opening an existing workbook."
End Sub

Now find your "thisworkbook" module under the Microsoft Objects category in your
project.
rightclick on it and select view code.
Paste this into that code window:

Option Explicit
Public WithEvents xlApp As Excel.Application
Private Sub xlApp_NewWorkbook(ByVal Wb As Workbook)
MsgBox "New workbook"
End Sub
Private Sub xlApp_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "Opening an existing workbook."
End Sub


Save this as addin (file|saveas and scroll down the save as type list).
remember the location where you saved it.

Then close excel.

reopen excel and choose
tools|addins
Select your addin--you may have to browse for it.

now try it out--open a workbook, start a new workbook.

(You'll replace the msgboxes with meaningful code, though.)

If you want to read more about application events, visit Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.htm

Chip also has some notes about events at:
http://www.cpearson.com/excel/events.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

David McRitchie also has notes at:
http://www.mvps.org/dmcritchie/excel/event.htm