Enter Creation Date in cell

[japumatrsi]

I have set up a spreadsheet which will be protected, allowing only a few cells for data entry by users. Need to have one cell automatically enter the file creation date (and time would be great) and that date not change when the file is re-opened on another date. Is this possible in Excel? I hate to redo this into a Word form.

 

[BrianB]

This information, among other properties, is available in File/Properties.

 

[japumatrsi]

How do I get it into the cell?

 

[lenze]

How do I get it into the cell?

Private Sub Workbook_Open()
    Sheet1.Range("$A$1").Value = ActiveWorkbook.BuiltinDocumentProperties.Item("Creation date")
End Sub

This goes in the ThisWorkBook module (Right Click the Excel Icon to the left of "File" on the menu bar and choose view code. You will need to format your cell as Date and Time.

Or You can run this sub (from a regular module) one time and then lock the cell.

Sub ShowCreationDate()
Sheet1.Range("$A$1").Value = ActiveWorkbook.BuiltinDocumentProperties.Item("Creation date")
End Sub

HTH

lenze[/code]

 

[japumatrsi]

I tried the first module for ThisWorkBook. When I protected the sheet and saved it, then reopened a few minutes later, I got a run time error. When I tried the sub from a regular module, the time changed whenever I re-opened the file. Can you tell me what I am doing wrong?

 

[lenze]

Make sure the target cell is unlocked and then protect the sheet. If that doesn't work, then we can add code to unprotect the sheet, insert the date, and reprotect the sheet. The time for creation date should not change. You should, however, be doing these in an existing workbook. Unfortunately, I'm currently at a machine now that doesn't have Excel, so I can't debug it for you. I'll check back later.

lenze

 

[japumatrsi]

No luck, does not hold the time, changes. Any further help would be appreciated.

 

[lenze]

No luck, does not hold the time, changes. Any further help would be appreciated.

I don't know what is happening. I placed this code in the ThisWorkbook module

Private Sub Workbook_Open()
    Sheet1.Range("$A$1").Value = ActiveWorkbook.BuiltinDocumentProperties.Item("Creation date")
End Sub

I then unlocked $A$1 and protected the worksheet. I saved the file. When I reopen it, the date and time of creation appears in $A$1. It does not change with subsequent reopenings.

I also placed this code

Sub ShowCreateDate()
    Range("$A$2").Value = ActiveWorkbook.BuiltinDocumentProperties.Item("Creation date")
End Sub

in a regular module. I unlocked $A$2 and protected the sheet. I ran the code. The same creation date and time was displayed in $A$2. After saving and reopening several minutes late, NOTHING Changed.

Sorry

lenze

 

[BrianB]

There can only ever be one Creation Date. :wink:

To see what built in properties you have in your Excel version run this :-
(you can also check out Custom Properties)

Sub test()
    rw = 1
    Set ws = Worksheets.Add
    For Each p In ActiveWorkbook.BuiltinDocumentProperties
        ws.Cells(rw, 1).Value = p.Name
        rw = rw + 1
    Next
End Sub

 

[japumatrsi]

Am sorry, don't think I was clear on what I need to do. I'm designing a template for multiple users. They click on the template, open a fresh xls file with new creation date for that file which they save with customer's account info. They are not be to be able to type or alter the creation date in their new file. (Need to make sure the time is not changed after it is saved with a new file name.) It's a Change Order Form for our manufacturing plant which will be emailed to various parts of the plant.

So, what do you think?