VBA Event?

[Misca]

I have a workbook template with some VBA code and I'd like to run a macro when new workbooks are created from the template. However, I can't seem to find the New Workbook event (the closest being Open Workbook which doesn't seem to work).

What would be the right event for the new workbook and where can I find it?

 

[Domski]

There isn't a new workbook event however the workbook open one should work. The code needs to be placed on the ThisWorkbook code page of the VBA project.

Dom

 

[Smitty]

There is a Workbook.New event, but it is a VSTO tool.

You should be able to use the Workbook_Open event. You can test if the opened wb name does not match the template name, i.e. If ThisWorkbook.Name <> "Template.xls", which indicates that it's been opened as a template copy, not the template itself. That will keep your code from firing if you need to modify the template.

Hope that helps,

EDIT: what he said.

 

[mikerickson]

There is an application level NewWorkbook event.
The way that I access it is through my Personal Macro Workbook.

This is in a Class module named AppLevelEvents

Public WithEvents thisApp As Application

Private Sub thisApp_NewWorkbook(ByVal Wb As Excel.Workbook)
    With Wb
        .PrecisionAsDisplayed = False
        .Date1904 = False
        .Saved = True
    End With
End Sub

and this in the ThisWorkbook module of the Personal Macro Workbook

Private Sub Workbook_Open()
    Set ThisApplication = New AppLevelEvents
    Set ThisApplication.thisApp = Application
End Sub

Your code should be substituted for mine in the NewWorkbook event.

 

[Misca]

Thanks, everybody and mikerickson especially for your help!

I think I can solve the problem with your advice.