Forum Discussion
Timestamps updating whenever I open excel
Hi, I am trying to create a time stamp so I can track how long a project takes to go from one step to another.
After formulas failed I went to VBA. I inserted a module with the code:
Function MyTimestamp(Reference As Range)
If Reference.Value <> "" Then
MyTimestamp = Format(Now, "mm/dd/yyyy hh:mm:ss")
Else
MyTimestamp = ""
End If
End Function
This works great except whenever I close the excel file and reopen it all the timestamps update to the current date and time.
Is there a formula I can use that will not do this?
Attached is an additional alternative suggestion.
Example in the attached file.
Hope this helps you further.
You need an event procedure for this instead of a function.
Let's say you enter the steps in S2:S50 and you want a timestamp in the corresponding cells in T2:T50.
Right-click the sheet tab.
Select View Code from the context menu.
Copy the following code into the worksheet module:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim cel As Range Set rng = Intersect(Range("S2:S50"), Target) If Not rng Is Nothing Then On Error GoTo ExitHandler Application.ScreenUpdating = False Application.EnableEvents = False For Each cel In rng If cel.Value = "" Then cel.Offset(0, 1).ClearContents Else cel.Offset(0, 1).Value = Now End If Next cel ExitHandler: Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub- HansVogelaar it is now saying calculation incomplete and is highlighting the first line of code. I'm not sure what I did wrong as this is my first time using VBA
In Excel, select File > Options > Formulas.
Make sure that 'Enable iterative calculation' is not ticked.
Attached is an additional alternative suggestion.
Example in the attached file.
Hope this helps you further.
