I want to create a spreadsheet that automatically enters the date that I started a task when I enter text into a field...that's simple enough with:
But that will change the date everytime I open the spreadsheet. I'm trying to set it up so the current date is placed in a cell and then stays that way.
So for example, I enter text in a cell, the date 11/10/2006 is entered in another cell and the date is fixed to 11/10/2006. I don't want it to change to 11/11/2006 when I open it tomorrow. Obviously the above formula will change the cell to whatever the current date is each time I open the spreadsheet.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 'adjust e2 to where your formula is.
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
Application.CutCopyMode = False
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'adjust e2 to where your formula is.
Range("E2").Value = range("E2").Value
beeyule, if you can describe more particulars of the situation, maybe a simple bit of code could achieve what you want. Such as this code (when in the code module for a worksheet) will enter the date in column A every time a change is made to a cell in a different column. Something similar can be done maybe to suit your requirements?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim cel As Range
Application.EnableEvents = False
For Each cel In Target
If cel.Column <> 1 Then Cells(cel.Row, 1).Value = Date
Set cel = Nothing
Application.EnableEvents = True