Showing Spreadsheet creation date from a template.

  • Thread starter Thread starter Andy Ralph
  • Start date Start date
A

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?
 
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
 
Back
Top