Insert Comment on Protected Worksheet

[MSingh]

Hi,

Sheet1 is p/word protected & has macros assigned to several objects/shapes.

How can i insert a comment on Sheet1 without enabling "Allow Users to Edit Objects" ?

The unprotected range where comments would be inserted is Sheet1 Range(F14:I213).
User should be able to edit the comment, comments will be printed-out.

Sub CommentAddOrEdit()
'www.contextures.com/xlcomments03.html#Plain
'method suggested by Jon Peltier 2006-03-04
'adds new plain text comment or adds text
'at end of existing comment text
Dim cmt As Comment
Set cmt = ActiveCell.Comment
If cmt Is Nothing Then
Set cmt = ActiveCell.AddComment
cmt.Text Text:=""
End If

'type to add comment text to selected shape
cmt.Visible = True
cmt.Shape.Select

End Sub

Many thanks for all the answers & alternatives you provide.

Regards
Mohamed

[HansV]

Your wishes are contradictory - a comment is an object, so users cannot edit it if you disable editing comments.

Instead, you can ask the user for the comment text in an input box, and set the comment text in code. You have to unprotect the sheet before doing so, and re-protect it afterwards.

Code: Select all

Sub CommentAddOrEdit()
  Dim cmt As Comment
  Dim strText As String
  Dim strOldText As String
  ActiveSheet.Unprotect Password:="secret"
  Set cmt = ActiveCell.Comment
  If cmt Is Nothing Then
    strOldText = ""
  Else
    strOldText = cmt.Text
  End If
  strText = InputBox(Prompt:="Enter the text for the comment", _
    Default:=strOldText)
  If strText <> "" Then
    If cmt Is Nothing Then
      Set cmt = ActiveCell.AddComment
    End If
    cmt.Text Text:=strText
  End If
  ActiveSheet.Protect Password:="secret"
End Sub

[MSingh]

Thank you Hans,

I knew you would find the solution!
With input box:
a. User cannot select any other object on sheet
b. If user cancels sheet1 remains protected.

Thank you for the prompt reply.
Regards
Mohamed