Results 1 to 2 of 2
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    California, USA
    Thanked 0 Times in 0 Posts

    Editing Cell Comments (XP)

    Is it possible to edit a cell comment under the following senario: The worksheet is protected and the cell containing the comment is unlocked and displayed.

    I have tried setting the worksheet protection to enable "editing objects" but this particular setting makes all object accessibile for editing which is contrary to what I want to accomplish. I have a number of comments displayed but only want to make one or two subject to editing.


  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts

    Re: Editing Cell Comments (XP)

    i don't know of any "direct" way to do this. Adding/editing comments are disabled when the sheet is protected (at least in XL97 I have found no direct way around it).Try this code.

    Add this to the worksheet object:

    <pre>Private Sub Worksheet_BeforeRightClick(ByVal Target As Excel.Range, Cancel As Boolean)
    Dim myBar As CommandBar
    Dim myButton As CommandBarButton
    Dim sMenu As String
    Dim sCaption As String
    Dim cmt As Comment
    sMenu = "mnuComment"

    If Not ActiveCell.Locked Then
    On Error Resume Next
    On Error GoTo 0
    Set cmt = ActiveCell.Comment
    If cmt Is Nothing Then
    sCaption = "Insert Comment"
    sCaption = "Edit Comment"
    End If
    Set myBar = CommandBars _
    .Add(Name:="mnuComment", Position:=msoBarPopup, Temporary:=False)
    Set myButton = myBar.Controls.Add(msoControlButton)
    With myButton
    .OnAction = "AddEditComment"
    .Caption = sCaption
    .Style = msoButtonIconAndCaption
    .FaceId = 1589
    End With
    With myBar
    If Not cmt Is Nothing Then
    .Controls.Add Type:=msoControlButton, Id:=1592
    .Controls.Add Type:=msoControlButton, Id:=1593
    End If
    End With
    End If
    ActiveSheet.Protect UserInterfaceOnly:=True
    End Sub</pre>

    and add this to a module:
    <pre>Sub AddEditComment()
    Dim cmt As Comment
    Dim sCmtText As String
    Dim iResponse As Integer
    Set cmt = ActiveCell.Comment

    If cmt Is Nothing Then
    sCmtText = ""
    sCmtText = cmt.Text
    End If
    sCmtText = Application.InputBox( _
    prompt:="Enter the Comment desired", _
    Title:="Get Comment", Default:=sCmtText, Type:=2)

    If sCmtText = "False" And Not cmt Is Nothing Then
    iResponse = MsgBox(prompt:="Do you want to Delete the comment?", _
    Buttons:=vbYesNo, Title:="Delete Comment?")
    If iResponse = vbYes Then cmt.Delete
    ElseIf sCmtText <> "False" And cmt Is Nothing Then
    ActiveCell.AddComment sCmtText
    ElseIf Not cmt Is Nothing Then
    cmt.Text Text:=sCmtText
    End If
    End Sub</pre>

    If you right click an unlocked cell:
    Worksheet is unprotected (password will have to be added if used)
    it will add a menu to add a comment (or if it already has a comment to edit, delete, or show)

    Add or Edit calls the add/edit macro which pulls up an inputbox to add your comment.
    Delete or show call the builtin routines to delete/show the comment.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts