Showing Spreadsheet creation date from a template.

[Andy Ralph]

Hi, I am trying to set up an Excel 2003 template for other users to use to
create new spreadsheets and return them to me.
I want to set a cell to show the Creation Date of the new spreadsheet,
created from the template.
If I set the cell in the template to
ActiveWorkbook.BuiltinDocumentProperties("Creation Date")
when I create a new file from the template, the cell still shows the date
the template was created, not the date the new spreadsheet was created.
Any ideas please?

 

[Sam Wilson]

Something like

range("a1").value = now

 

[Jacob Skaria]

Hi Andy

Suppose the template sheet is named 'Template' and the date cell in the
template sheet is kept blank; try the below code..which used workbook sheet
activate event.
From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the
left treeview search for the workbook name and click on + to expand it.
Within that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh.Name <> "Template" And Sh.Range("A1") = "" Then
Sh.Range("A1") = Now
End If
End Sub

If this post helps click Yes