Add comment with the entry date and time

[Tankerman]

Hey All,

A while back I used a vb I found here to lock cells that have been edited and it works great; I was wondering if there is anyway to add a comment with the date and time of the entry.

The code I use is below

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Answ As String
Application.ScreenUpdating = False
Answ = MsgBox("The cell will now be locked with your selection.", vbOKCancel, "Confirm Change")
  If Answ <> vbOK Then
  Application.EnableEvents = False
  Target.ClearContents 'clear contents if cancel is pressed
  Application.EnableEvents = True
  Exit Sub
  End If
ActiveSheet.Unprotect "1"
Target.Locked = True
ActiveSheet.Protect Password:="1", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub

Thanks for the help

 

[Gary McMaster]

Maybe this (additions in blue)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Answ As String
Application.ScreenUpdating = False
Answ = MsgBox("The cell will now be locked with your selection.", vbOKCancel, "Confirm Change")
  If Answ <> vbOK Then
  Application.EnableEvents = False
  Target.ClearContents 'clear contents if cancel is pressed
  Application.EnableEvents = True
  Exit Sub
  End If
  [COLOR=#0000ff]Target.AddComment
  Target.Comment.Text Text:=Str(Now)[/COLOR]
ActiveSheet.Unprotect "1"
Target.Locked = True
ActiveSheet.Protect Password:="1", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True

End Sub

 

[Tankerman]

Gary,

Works great except I had to move the 2 lines down one line to be below the unprotect line; you are the man

Thanks

 

[Gary McMaster]

That makes sense. I didn't have any protection set so I missed that snag. Glad it works for you.

Gary