I forgot to say, the PutComment sub needs to be placed in a standard module,
and change "Yes" to "True" (or put these two lines at the top of the standard
module)

Global Const Yes As Boolean = True
Global Const No As Boolean = False


"Charlie" wrote:


If Target.Count=1 And Target.Column = 1 Then
ActiveSheet.Unprotect
PutComment "Cell Last Edited: " & Now & " by " &
Application.UserName, Target
ActiveSheet.Protect
End If

Public Sub PutComment(txt As String, Optional Cell As Range)
'
' remove the old comment (if any)
'
If Cell Is Nothing Then
ActiveCell.ClearComments
Else
Cell.ClearComments
End If
'
' add a new comment
'
If txt < "" Then
If Cell Is Nothing Then
ActiveCell.ClearComments
ActiveCell.AddComment
ActiveCell.Comment.Text txt
ActiveCell.Comment.Shape.TextFrame.AutoSize = Yes
Else
Cell.ClearComments
Cell.AddComment
Cell.Comment.Text txt
Cell.Comment.Shape.TextFrame.AutoSize = Yes
End If
End If
'
End Sub


"buckag" wrote:

I am a novice to VBA, but have included the following code to insert a
comment field with the date/time and user name:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
comment = ("Cell Last Edited: ") & Now & (" by ") & Application.UserName
Target.Cells.NoteText comment
End If
End Sub


I have a range of cells that are allow particular users to update these
cells. When I turn on the spreadsheet protection I am getting the following
error:

Runtime 1004 Note text Method of Range class failed, and the debugger takes
me to Target.Cells.NoteText comment.

My question is, is there additional code needed? Or is my code bad when
using spreadsheet protection? How can I correct my code?

Any help is appreciated