macro that runs only when workbook is created

  • Thread starter Thread starter BRC
  • Start date Start date
B

BRC

Can anyone tell me if excel has this capability built in? I know in
word you can create a autonew macro that only runs when the document
is created (doesn’t run when reopened). Is this possible in excel.
Using excel 2007. I want to create a new workbook from a template and
I would like to have a macro run when the workbook is created. Thanks
for any advice. BRC
 
You need to use Application level events. In the ThisWorkbook code
module, paste the following:


Private WithEvents App As Excel.Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
MsgBox "New Workbook: " & Wb.Name
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub


Now, save, close, and re-open the workbook. As long as this workbook
is open, the NewWorkbook event will run when a new workbook is
created.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
You need to use Application level events. In the ThisWorkbook code
module, paste the following:

Private WithEvents App As Excel.Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    MsgBox "New Workbook: " & Wb.Name
End Sub

Private Sub Workbook_Open()
    Set App = Application
End Sub

Now, save, close, and re-open the workbook. As long as this workbook
is open, the NewWorkbook event will run when a new workbook is
created.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

Can anyone tell me if excel has this capability built in?  I know in
word you can create a autonew macro that only runs when the document
is created (doesn’t run when reopened). Is this possible in excel.
Using excel 2007.  I want to create a new workbook from a template and
I would like to have a macro run when the workbook is created. Thanks
for any advice. BRC- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Chip, Thanks for the response. I got this to work a couple of times
but I am not sure what i did. What I was trying to do is as follows.
I have an excel template (newjob.xltm) I wanted to run a macro when I
create a new file based on this template. So I open excel, click new/
my templates and i get a new workbook based on the newjobxltm
template. I was trying to make a macro run that when that new file is
created. From what your post says it seems I have to have a 3rd file
that I open first and then create a new file based on my template.
Some where i must have made a mistake becuase when I try to create a
new file based on newjob.xltm I am getting a "only valid in object
module" msgbox.
 
Chip's suggestion will work whenever a new workbook is created.

If you want a macro to to run when the template is opened, you can use a macro
in your template file.

You could use the workbook_open event. This code goes into the ThisWorkbook
module of the template file.

Option Explicit
Private Sub Workbook_Open()

If Me.Path <> "" Then
Exit Sub
End If

MsgBox "New workbook--your real macro here"

End Sub

Or you could use the Auto_Open procedure. This code goes into a General module
-- not behind a worksheet and not behind ThisWorkbook.

Option Explicit
Sub Auto_Open()

If ThisWorkbook.Path <> "" Then
Exit Sub
End If

MsgBox "New workbook--your real macro here"

End Sub

=========
The keyword Me in the first code refers to the object that owns the code--in
this case, it refers to the workbook/template being opened.

And by checking the Path, the code is checking to see if the file has been
saved. If it has been saved, then the path isn't "" and you're not creating a
new workbook based on the template--you're opening an existing workbook.


You need to use Application level events. In the ThisWorkbook code
module, paste the following:

Private WithEvents App As Excel.Application

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
MsgBox "New Workbook: " & Wb.Name
End Sub

Private Sub Workbook_Open()
Set App = Application
End Sub

Now, save, close, and re-open the workbook. As long as this workbook
is open, the NewWorkbook event will run when a new workbook is
created.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLCwww.cpearson.com
[email on web site]

Can anyone tell me if excel has this capability built in? I know in
word you can create a autonew macro that only runs when the document
is created (doesn’t run when reopened). Is this possible in excel.
Using excel 2007. I want to create a new workbook from a template and
I would like to have a macro run when the workbook is created. Thanks
for any advice. BRC- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Chip, Thanks for the response. I got this to work a couple of times
but I am not sure what i did. What I was trying to do is as follows.
I have an excel template (newjob.xltm) I wanted to run a macro when I
create a new file based on this template. So I open excel, click new/
my templates and i get a new workbook based on the newjobxltm
template. I was trying to make a macro run that when that new file is
created. From what your post says it seems I have to have a 3rd file
that I open first and then create a new file based on my template.
Some where i must have made a mistake becuase when I try to create a
new file based on newjob.xltm I am getting a "only valid in object
module" msgbox.
Click to expand...
 
Back
Top