Insert comment in protected worksheet


G

Guest

What are the correct settings that will allow a user to insert a cell comment
in an unprotected cell of a protected worksheet? When the worksheet is
protected, the insert comment function is not available (greyed out).
 
Ad

Advertisements

G

Guest

You could have the user run this macro. The major drawback is that if a user
wishes to edit an existing comment, they can't. They'd have to insert a new
comment over the old one:

Sub InsertComment()
Dim strPassword As String
Dim strCommText As String
Dim strMsg As String
Dim strMsg2 As String
Dim nMsgResp As Long
Dim strOldComm As String

strPassword = "apple" 'change to your password
strMsg = "Comment already in cell! Do you want to insert a new comment?"
strMsg2 = "Insert text for comment in "

With ActiveCell
If Not .Comment Is Nothing Then
strOldComm = .Comment.Text
nMsgResp = MsgBox(strMsg, vbYesNo)
If nMsgResp = vbYes Then
.Comment.Delete
GoTo NewComment
Else
Exit Sub
End If
Else
NewComment:
strCommText = InputBox(strMsg2 & .Address(False, False) & ":")
ActiveSheet.Unprotect Password:=strPassword
.AddComment
If strCommText = "" Then
.Comment.Text Text:=strOldComm
Else
.Comment.Text Text:=strCommText
ActiveSheet.Protect Password:=strPassword
End If
End If
End With

End Sub
 
Ad

Advertisements

D

Dave Peterson

If you allow the users to "Edit objects"

In xl2002+
Tools|protect|protect sheet
there's a list of things that you want to allow the users to do.

If you check "edit objects", then they can insert comments (and do other things
to other objects--pictures, buttons...)

In xl2k and before, I think there's an checkbox on that dialog.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top