2016 Excel - Automatic date and time when you insert a comment

[Terrifro]

I am currently using 2016 Excel and I want to add an automatic Date and Time when I insert a comment in a cell.

Does anyone have a code that can be insert in the spreadsheet, or is it easier then that.

Please help.

 

[Trevor G]

Keyboard shortcut for adding the date is Ctrl+; to add time use Shift + Ctrl + ;

 

[Yongle]

Goes in SHEET code (not a module)
Double click in cell to add the comment
click in any other cell to make it non-visible

Option Explicit
Private tCell As Range
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
    Set tCell = Target
    Target.AddComment
    Target.Comment.Visible = True
    Target.Comment.Text Text:=""
    Target.Comment.Text Text:=Format(Now, "dd/mm/yy hh:mm")
On Error GoTo 0
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    tCell.Comment.Visible = False
    On Error GoTo 0
End Sub

 

[Terrifro]

Keyboard shortcut for adding the date is Ctrl+; to add time use Shift + Ctrl + ;

Thank you Trevor G, however I need the date and time to appear automatic in a comment every time someone adds a comment.

 

[Terrifro]

Goes in SHEET code (not a module)
Double click in cell to add the comment
click in any other cell to make it non-visible

Option Explicit
Private tCell As Range
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
    Set tCell = Target
    Target.AddComment
    Target.Comment.Visible = True
    Target.Comment.Text Text:=""
    Target.Comment.Text Text:=Format(Now, "dd/mm/yy hh:mm")
On Error GoTo 0
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    tCell.Comment.Visible = False
    On Error GoTo 0
End Sub

Thank you Yongle it works perfect

 

[Terrifro]

Thank you Yongle it works perfect

Thank you Yongle it works perfect however if I want to add more information in the comment at a later date, I want a new date and time to automatically appear above the previous comment.
example below.

 

[Yongle]

Option Explicit
Private tCell As Range
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
    Dim cTxt As String, dTxt As String
    Set tCell = Target
    dTxt = Format(Now, "dd/mm/yy hh:mm") & Chr(10) & Chr(10)
    cTxt = Target.Comment.Text
    cTxt = dTxt & cTxt
    Target.AddComment
    Target.Comment.Visible = True
    Target.Comment.Text Text:=cTxt
On Error GoTo 0
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    tCell.Comment.Visible = False
    On Error GoTo 0
End Sub

 

[Terrifro]

Option Explicit
Private tCell As Range
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
On Error Resume Next
    Dim cTxt As String, dTxt As String
    Set tCell = Target
    dTxt = Format(Now, "dd/mm/yy hh:mm") & Chr(10) & Chr(10)
    cTxt = Target.Comment.Text
    cTxt = dTxt & cTxt
    Target.AddComment
    Target.Comment.Visible = True
    Target.Comment.Text Text:=cTxt
On Error GoTo 0
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    tCell.Comment.Visible = False
    On Error GoTo 0
End Sub

Thank you so much - it works perfectly. your amazing.