Workbook_Open Sub, is there an equivalent Workbook_New for templates

ACCtionMan

New Member
Joined
Sep 28, 2004
Messages
27
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.
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
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.

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

ACCtionMan

New Member
Joined
Sep 28, 2004
Messages
27
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,647
Messages
5,626,069
Members
416,160
Latest member
SanbiVN

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top