CODEWITHSUNDEEP
excelvba
dijkie
dijkie

Reputation: 3

How to create timestamp?

For a new template a log file is needed. In this log file, a timestamp is created once an individual has signed off a specific item.

This is done by the following:

Function Timestamp(Reference As Range)
    
If Reference.Value <> "" Then    
    Timestamp = Format(DateTime.Now, "dd-mm-yyyy hh:mm:ss")  
Else    
    Timestamp = ""
End If

End Function

I've used the function type, as this code needs to run on several sheets.

It can happen that a certain sheets need to be copied, which triggers this macro to run again and to adjust the previous timestamps which therefore do not correspond to the time it was signed off.

I'm looking for a way to adjust this code to not run again once a sheet is copied.

I've tried the additional code, however it does not work properly.

Function Copyasvalue(Reference As Range)
    
If Reference.Value <> "" Then
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
Else
    Copyasvalue = ""
End If
    
End Function

Upvotes: 0

Views: 813

Answers (2)

Andreas
Andreas

Reputation: 23968

You can use the Workbook_SheetChange() to do what you want.

In Workbook object of the vba project add this function: 

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        ' If Not Intersect(Target, Me.Range("B1")) Is Nothing Then  'if you want to be able to copy paste as @Pᴇʜ explains in comments below
        If Target.Address = "$B$1" Then 'cell where name is entered. if only entered by typeing
        Range("A1").Value = Format(DateTime.Now, "dd-mm-yyyy hh:mm:ss") 'cell where date time should be etered
    end if
End Sub

with the variable sh you can limit it to only run in some sheets.
if not sh.name = "Don't run on this sheet" then to make sure it does not run on that sheet.

The datetime will not change unless you change the "name cell".

Upvotes: 1

DonkeyTheKong
DonkeyTheKong

Reputation: 37

So after what I understand:

You can call a Sub like: Call timeStamp

Private Sub timeStamp()
Dim time As String

time = Format(now, "dd-mm-yyyy hh:mm:ss")
Debug.Print time
MsgBox time
Range(A1).Value = time

End Sub

Upvotes: 0

Related Questions