Excel Dates!!

S

slvtenn

how can i lock a cell to where it want change after save and close.
Example: if i use =TODAY() in a cell, everytime i open that spreadsheet
the current date will appear even after i save it and i need the
original. Any help would be greatly appreciated!!
 
D

Doug Kanter

slvtenn said:
how can i lock a cell to where it want change after save and close.
Example: if i use =TODAY() in a cell, everytime i open that spreadsheet
the current date will appear even after i save it and i need the
original. Any help would be greatly appreciated!!
Click to expand...

Are you saying that you want the date to change every time you open the
sheet, or not?
 
K

Kevin B

You can put the following VBA code in the Workbook module in the OnOpen event
to set the date in cell A1 to the current date if there is not a date value
already present. If there is a date the code does nothing

======================================================
Private Sub Workbook_Open()

Dim wb As Workbook
Dim ws As Worksheet
Dim varVal As Variant
Dim r As Range

Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet1")
Set r = Range("A1")
varVal = r.Value

If IsDate(varVal) Then
GoTo exitWBOpen
Else
ws.Unprotect
r.Value = Date
ws.Protect
End If

exitWBOpen:

Set wb = Nothing
Set ws = Nothing
Set r = Nothing
Exit Sub

End Sub
======================================================
 
D

Doug Kanter

slvtenn said:
No i want it to stay with the original date that the sheet was saved.
Click to expand...

OK....stay tuned for about an hour. I can't start Excel right now, due to
too many other apps running. But, I did something like this a few years
back, to keep track of how often people were using a sheet I made for them.
Hopefully, I still have it here somewhere. If you want to experiment in the
meanwhile, it was based on two very simple things:

1) If you put the cursor in that date cell which contains =NOW(), do CTRL-C,
then Edit, Paste Special, Value, it'll replace the formula with a static
version of the time at that moment. I recorded these keystrokes - very
simple.

2) It's possible to run a macro every time a sheet's opened or closed. Poke
around on google for "auto open macro excel", and you should also find the
method for running a macro automatically when a sheet closes.

The only thing to think about is that the NEXT time you close the sheet,
that cell will contain the static date, not the =NOW() formula. There are a
few simple ways around this.
 
Top