Workbook_Open Sub, is there an equivalent Workbook_New for templates

[ACCtionMan]

I am setting up an Excel template and would like to run some VBA, but only if it's a new workbook, ie the default when double clicking a 'xlt' file.

I can get the code to run using the 'Workbook_Open' event.

 

[jbeaucaire]

Several ways to do this, but one would be to put a value off in a distant cell....AZ1...prior to saving your template.

Then design your macro so that the first thing it does is look to see if cell AZ1 is blank. If it's not, the macro runs and part of the macro is to clear the contents of cell AZ1. It will never pass the "IF" test after that, so the code never runs again.

If Range("AZ1").Value <> "" Then
    Range("AZ1").ClearContents
    [your macro code]
End If

 

[ACCtionMan]

Thanks, I had added the following code so as to only display a user form if a named range (did this is case I decide to change the cell location later) was blank.

Private Sub Workbook_Open()
If Trim(Range("WRNum")) = "" Then ' run this code if work request number not populated
frm_WRNumber.Show
End If
End Sub

This does the trick of only running the full code if the named range is = "". The problem was that even this small bit of code seems to slow down the opening of the document.

Word has a 'Document_New' event so I thought Mr Microsoft's team might have added the same to Excel.