Excel Dates!!
 

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!!

Excel Dates!!
 

"slvtenn" wrote in message
oups.com...
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!!


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

Excel Dates!!
 

No i want it to stay with the original date that the sheet was saved.

Excel Dates!!
 

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
================================================== ====
--
Kevin Backmann


"slvtenn" wrote:

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!!

Excel Dates!!
 

"slvtenn" wrote in message
oups.com...
No i want it to stay with the original date that the sheet was saved.


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.

Excel Dates!!
 

Thanks that worked great!!