Creating non-volatile Date

[Nigel]

Hi All
I have a requirement to, from within VBA, store the
current date into a worksheet.cell. But for that date to
be non-volatile.

If I use the system date functions then this will always
change, after the worksheet is reloaded. What is the best
approach to overcome this?

Cheers
Nigel

 

[Harald Staff]

Hi Nigel

Sub DateMe()
Sheets(1).Range("C14").Value = Date
End Sub

HTH. best wishes Harald

 

[windsurferLA]

Hi All
I have a requirement to, from within VBA, store the
current date into a worksheet.cell. But for that date to
be non-volatile.

If I use the system date functions then this will always
change, after the worksheet is reloaded. What is the best
approach to overcome this?

Cheers
Nigel

I suspect what you want to do is write a macro to
(1) copy the cell containing the date
and
(2) paste the contents of the cell back into the cell using PASTE
SPECIAL / VALUE

You can obtain the appropriate code by merely recording a macro of the
operation being performed.

 

[Ed]

If this is going to be used in a Workbook_Open event macro or called
automatically from another procedure, wrap it in an IF. That way, if it's
already been done once, it won't change unexpectedly.
' Assuming A1 has the non-volatile date,
If Range("A1") <> "" Then
' code to capture date
End If

Ed

 

[john.opie]

Hi All
I have a requirement to, from within VBA, store the
current date into a worksheet.cell. But for that date to
be non-volatile.

If I use the system date functions then this will always
change, after the worksheet is reloaded. What is the best
approach to overcome this?

Cheers
Nigel

Hi -

This puts the both the data and the time in cell A1:

ThisWorkbook.Sheets("NameOfSheet").Cells(A1).Value = Now()

where you replace "NameOfSheet" with the worksheet name that you
actually are using. Also adjust the Cells(A1) to the proper cell
reference that you need.

By using the attribute ".Value" of the function "Now()" in ".Cells(A1)"
of the worksheet "ThisWorkbook.Sheets("NameOfSheet")" you get the
nonvolatile results of the function "Now()". It's a great way of
timestamping any and all workbooks and worksheets...

Hope this helps

John