Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for inserting Comment field with spreadsheet protection
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for inserting Comment field with spreadsheet protection
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for inserting Comment field with spreadsheet protection
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for inserting Comment field with spreadsheet protection
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code for inserting Comment field with spreadsheet protection
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a comment box in a protected worksheet | Excel Discussion (Misc queries) | |||
Can you print indicators when inserting a comment? | Excel Worksheet Functions | |||
Protection will not allow me to insert a comment | Excel Worksheet Functions | |||
Protection will not allow me to insert a comment | Excel Discussion (Misc queries) | |||
Inserting a chart instead of text into comment box | Excel Programming |