Creating non-volatile Date

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 Nigel

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

HTH. best wishes Harald

"Nigel" <[email protected]> skrev i melding
news:[email protected]...
> 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

Nigel wrote:
> 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.

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

"windsurferLA" <[email protected]> wrote in message
news:[email protected]...
> Nigel wrote:
> > 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.

Nigel wrote:
> 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