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

Worked fine for me. To try something else, I would declare the comment and
change the variable to another name. Don't know if this will help but try...

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

--
Good Luck and hope this helps.
If this post was helpfull, please remember to click on the ''''YES''''
button at the bottom of the screen.
Thanks,
Gary Brown


"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
>

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
>

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
> >

After I wrote this, I did think of turning off the password and turning it
back on as well. I did a little differently but it worked thanks to both
for your help, both ways are useful. It is so much fun to finally start
programming VBA than trouble shooting every couple years....

"Charlie" wrote:

> 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
> > >