Creation Date

[pjd]

Hi

I was given this reply to me ealier question yesterday. (Thanks Bob)

Private Sub Workbook_Open()
Worksheets(1).Range("A1").Value =Format(Date,"dd mmm yyyy")
End Sub

I just have two questions

1) The worksheet it should appear in is named Input Sheet. How do I change the code? ( I am less than a newbie to VBA)

2) Will this ensure that when a new spreadsheet is opened based on template and the date is automatically entered by the code, will it ALWAYS remain the same date, even if I open that particular saved spreadsheet 6 months later?

thanks

 

[JE McGimpsey]

Couple of things:

First: using

Format(Date, "dd mmm yyyy")

won't give you the date in that format unless that is already your
cell's format (or it's your default date format). XL's parser will read
it as a date, and display it in whatever date format you have set.
Instead you can set the format directly.

To change the sheet name:

Private Sub Workbook_Open()
With Worksheets("Input Sheet").Range("A1")
.Value = Date
.NumberFormat = "dd mmm yyyy"
End With
End Sub

This will put the current date into 'Input Sheet'!A1 whenever the file
is opened.

If you only want it to do this once, then keep that date intact, check
for an entry first (just make sure you save the template with the cell
blank):

Private Sub Workbook_Open()
With Worksheets("Input Sheet").Range("A1")
If IsEmpty(.Value) Then
.Value = Date
.NumberFormat = "dd mmm yyyy"
End If
End With
End Sub