How do I stop =NOW() or =TODAY() data changing when file saved and then reopened

S

slopey

I have a form which uses =NOW() in seperate cells to set the date an
another to set the time - cells formatted accordingly - but I want th
date and time to be saved when I close and save the file so that when
open the file again to review the form contents, the date and tim
remain as set when originally opened.

I used the non-asterisked date format but it still updates when th
file is opened
 
N

Norman Harker

Hi Slopey!

Ctrl + ; gives you the system date
Ctrl + : gives you the system time
Ctrl + ; <space> Ctrl + : gives your system date and time

None of these are updated when the workbook is recalculated.

You can apply format on these to taste.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
P

Peter Atherton

Slopey
use this - copy it into a VB Module ALT+F11, Insert Module
then paste the function

Function mytime()
Static mt
mt = Now()
mytime = mt
End Function

to use the function type =mytime(). Format the cell as you
like

to update the function press Ctrl + alt + F9

Regards
Peter
 
T

Tom Ogilvy

Just a thought:
to update the function press Ctrl + alt + F9
Click to expand...

or put another way, anytime you do Ctrl+Alt+F9, it will update. If you
don't use that combination, then it may not be a problem.
 
S

slopey

Peter & Norman
Thanks for the help

As always it is not quite that simple

I need the auto update facility of =Now() when I load the blank but
need to save the values when I save the form in another file.

The module function sets the date & time on the blank form and the
Ctrl+Alt+F9 is awkward to remember to do as I am not in that area of
the form.

Thanks
 
N

Norman Harker

Hi slopey!

Is what your saying that you want to datestamp on opening but not
otherwise?

This is possible with a Workbook_Open event handling subroutine.

Or you could have a simple datestamp attached to a button.

But how do you want to treat a subsequent opening of the workbook?

I think you need to define precisely when you want the datestamping to
take place.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
S

slopey

Thanks
I have a blank estimate form as a file.

When I get a call the form is opened and I then want it date stamped
with date and time in two seperate cells - =Now() works perfectly for
this.

When the caller has finished and I have quoted I save the estimate
sheet to a file so that I have a record of the prices etc quoted and
when.
I therefore need the time and date stamp to be retained as values so
that I can trace what I quoted and when - like a copy,special paste,
values.

The original form file has to remain untouched ready for the next
caller.

I tried the option of the module and now cannot get rid of the module
from the worksheet. I cannot see it to delete it anywhere albeit shows
up in list under UDF.

Hope this is clearer.

Many thanks.
 
N

Norman Harker

Hi slopey!

Looks like two points here but please confirm.

1. You want to freeze the date / time when you save
2. You *only* want to do this if you are saving under a different name
than your original form.

To get rid of that worksheet_change subroutine

Right click the sheet that it operates on.
Select "View Code"
Select the code and Cut

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
N

Norman Harker

Hi Slopey!

You might use this approach:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
If Application.ActiveWorkbook.Name <> "testsave.xls" Then
If Range("Datecell").Value = "" Then
Range("Datecell").Value = Now
End If
End If
End Sub

However, this doesn't seem to fire for a Before SaveAs case and the
new workbook could be closed without the date stamp unless there is
another change.

Unless anyone has a way around this I'd use a Workbook_BeforeClose
event handler like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Application.ActiveWorkbook.Name <> "testsave.xls" Then
If Range("Datecell").Value = "" Then
Range("Datecell").Value = Now
Application.ActiveWorkbook.Save
End If
End If
End Sub

I can't say I'm happy with this and I'd recommend waiting to see if
anyone comes up with a better suggestion. Very likely in my view.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
S

slopey

Hi Norman
Many thanks for giving this matter so much thought.
I must admit to getting somewhat lost within the arguments but wil
take your word for it - perhaps I ought to buy the book!!

This is much the same issue as the person posted on 2/1/04 (by New t
Excel) wanting an invoice number counter - another problem I have.

As you advise I'll sit tight for now and see if any other replies com
in.

Thanks again for your help
 
N

Norman Harker

Hi slopey!

Keep watching! I'm certain that my approach is not the best but can't
seem to find / think of a better one.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
D

Dave Peterson

That first routine fired for me when I did File|SaveAs.

Maybe you disabled events while testing???

I would watch out for upper and lower case, though:

if lcase(me.name) <> lcase("testsave.xls") then
 
N

Norman Harker

Hi Dave!

Thanks for the test. I'll give it a few more runs. Also for upper /
lower case caution.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
[email protected]
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Top