CODEWITHSUNDEEP
exceltemplatesexcel-2010vba
kmote
kmote

Reputation: 16735

automatically run excel macro upon creation from template

I want to create an excel template that will includes formulas for dating the columns. However, since those formulas will be based on TODAY(), I need to convert them to static strings (so the dates don't change everytime someone opens it). Is there a way to add a macro that will run automatically when someone creates a new spreadsheet based on the template? (Similarly to Auto_Open(), only on Create, rather than Open). If so, I could just create a macro that will replace the formulas with their results upon document creation. Can this be done?

[Note: I'm not married to this solution; it just seemed like the simplest way to protect my spreadsheet. If someone can suggest an alternative approach, I'd be obliged.]

Upvotes: 1

Views: 2495

Answers (2)

kmote
kmote

Reputation: 16735

I realized that there is no need for a Workbook_Create() function, as that behavior can be emulated by simply deleting the macro after it has run once (which happens when it is first created). Deleting macros is done automatically when the file is saved with a .xlsx extension. In addition, you need to prevent the macro from running when you open the template itself (while editing the it). You can hold the SHIFT key when opening it to prevent auto-run macros, but since that method isn't foolproof, I added this code at the top:

Private Sub Workbook_Open()
    'If we're opening the template, don't run the macro
    If Application.ActiveWorkbook.FileFormat = xlOpenXMLTemplateMacroEnabled Then
        Exit Sub
    End If
    ...
    'add code here to SaveAs .xlsx, thus removing the macros, so it won't run every time.
End Sub

(Note: I didn't show my SaveAs code as it is rather messy: I wanted to suppress the default warning about losing macros, but wanted to also protect the user from inadvertantly overwriting previous file. If anyone is interested, I could post it)

Upvotes: 0

MakeCents
MakeCents

Reputation: 764

I have a couple thoughts...

  • If you run a copy/paste values macro every time it really won't matter, right?
  • You could check if the file exists yet (has been saved), and if not then this must be the template opened as a new workbook, maybe?

Code:

Private Sub Workbook_Open()
    If Dir(ActiveWorkbook.Name) = "" Then
        'run the macro?
        MsgBox "I'm gonna run this macro"
    End If
End Sub
  • You could have a cell on one of the sheets, that will never be used, or is hidden, that will store whether or not to run the macro, and change that when the file is opened or when the macro is ran. Then have a macro run on open that checks that cell. (Or custom/document property)
  • You could populate the cells that have the today() formula only on open and if they are already populated then don't run the macro?

Upvotes: 2

Related Questions