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.
6 Replies
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
Sample workbook:
