Excel VBA Question: No "Document_New()"?

[Tim]

I know when creating a template in Word, I can have it do certain things when
the person selects to create a new document from the template, and place those
commands in the Document_New() location.

Does Excel have anything like this? All I can find is Workbook_Open() event....
I'd like to find some way of accomplishing a Workbook_New() event, where only
when you create a new document based on the template do those events occur.

Can anyone provide me with any solutions?

Thanks!

 

[Chip Pearson]

Tim,

Put the following code in the ThisWorkbook code module:


Public WithEvents App As Application
Private Sub Workbook_Open()
Set App = Application
End Sub

Private Sub App_NewWorkbook(ByVal Wb As Excel.Workbook)
'
' code for new workbook
'
End Sub

 

[Tim]

Thanks for the rather speedy response...

But a question:

Does it matter that the template already has one sheet. All I'm wanting to do is
when the user creates a new document from the template, it prompts them for
information, and auto-fills out the spreadsheet for them. But, problem is, when
they go back to open up the document, it goes thru that process again and I want
it not to do that.

I tried the code you gave me and tried it. Sadly, the prompt for the
information does not come up when I select to create a new document from the
template...

Or... is it going to be easier if I were to just add a number of "Spaces" to a
certain cell, and check that cell for contents upon startup the next time?

 

[Jezebel]

Add some code that checks whether the function has been run before, exit if
yes.

 

[Cindy Meister -WordMVP-]

Hi Chip,

Put the following code in the ThisWorkbook code module:

Cindy