Hi all,

Merry christmas .

I need to insert a comment of user name, date and time automatically when a
cell is changed or in next cell user name , date and time should come
automatically.
date should not be changed to current date while reopen.

thank you.

Merry Christmas to you as well

Insert this macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
n = ActiveSheet.UsedRange.SpecialCells(xlComments).Cou nt
If n = 0 Then
Else
Set r = ActiveSheet.UsedRange.SpecialCells(xlComments)
If Intersect(r, Target) Is Nothing Then
Else
Target.Comment.Delete
End If
End If
With Target
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Now() & " " & Environ("username")
End With
End Sub

Whenever a cell is changed via user input, the date, time, and username are
recorded in a comment in the cell.

REMEMBER: the worksheet code area, not a standard module.
--
Gary''s Student - gsnu200762


"Rod" wrote:

Hi all,

Merry christmas .

I need to insert a comment of user name, date and time automatically when a
cell is changed or in next cell user name , date and time should come
automatically.
date should not be changed to current date while reopen.

thank you.

Thank you Gary.
How do stop others to edit or delete that comment?
thanks in advance

"Gary''s Student" wrote:

Merry Christmas to you as well

Insert this macro in the worksheet code area:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
n = ActiveSheet.UsedRange.SpecialCells(xlComments).Cou nt
If n = 0 Then
Else
Set r = ActiveSheet.UsedRange.SpecialCells(xlComments)
If Intersect(r, Target) Is Nothing Then
Else
Target.Comment.Delete
End If
End If
With Target
.AddComment
.Comment.Visible = False
.Comment.Text Text:=Now() & " " & Environ("username")
End With
End Sub

Whenever a cell is changed via user input, the date, time, and username are
recorded in a comment in the cell.

REMEMBER: the worksheet code area, not a standard module.
--
Gary''s Student - gsnu200762


"Rod" wrote:

Hi all,

Merry christmas .

I need to insert a comment of user name, date and time automatically when a
cell is changed or in next cell user name , date and time should come
automatically.
date should not be changed to current date while reopen.

thank you.