Excel Date - Auto enter file creation date (free chocolate for help)

A

arielax

want to be able to have a cell auto fill the first time the sheet i
saved so i have the creation date. Then if poss - never let them chang
the cell so it keeps record of when the file was first created.

Thanks - any help will get some free chocolate! ;-)

Arie
Astor Chocolate Corp.
www.astorchocolate.co
 
A

Andy Brown

Try some code in the workbook module ; e.g.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Range("Sheet1!A1") = "" Then
Range("Sheet1!A1") = Date
End If
End Sub

Then set Sheet1's Visible property to VeryHidden.

However, chocolate solves nothing.

HTH,
Andy
 
F

Frank Kabel

Hi
put the following code in your workbook module (not in a standard
module)

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim wks as worksheet
set wks = me.worksheets("sheet1")
with wks.range("A1")
if .value ="" then
.value = date
.numberformat = "MM/DD/YYYY"
end if
end with

End Sub
 
J

Jack Sons

Couldn't it be extracted from file>properties? That is where I would look
first.

Jack Sons
The Netherlands
 
A

arielax

Thank you for the replys.

I am not sure on how this is supposed to work?

When I add a module and paste your code into it nothing happens.

I need the date in "K15"

I am sure I am doing something wrong as to where i am putting thi
code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim wks as worksheet
set wks = me.worksheets("sheet1")
with wks.range("A1")
if .value ="" then
.value = date
.numberformat = "MM/DD/YYYY"
end if
end with

End Sub


nothing is happening?

thanks
 
A

Andy Brown

I am sure I am doing something wrong as to where i am putting this
code:
Click to expand...

You are ; specifically,
When I add a module and paste your code into it nothing happens.
Click to expand...

You should use Frank's code as he knows very much more than I. We both said
that the code needs to go in the workbook module. To access this, rightclick
the Excel icon left of File in the menu bar, select View Code.

Rgds,
Andy
 
Top