Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Jan 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to add comments to a protected sheet (XP)

    Does anyone know how to add a comment to cell that has a validation list (ie a drop down list), in a protected sheet?

    I have a simple routine that brings up an InputBox when the user double clicks on the cells that they want the comment in. This is attached to the BeforeDoubleClick event of the sheet. This works fine on all cells in an unprotected sheet.

    For a protected sheet I have tried to add code to turn the protection off (and back on), but this only works for cells without a validation list! This is the code:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim z As Variant

    Application.CutCopyMode = False
    ActiveSheet.Unprotect

    If Selection.Column = 4 Or Selection.Column = 40 Then
    z = InsertDate()
    Else
    z = InsertComment()
    Cancel = True
    End If

    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveSheet.EnableSelection = xlUnlockedCells

    End Sub

    ---------------------------------------------------------------------------------------------------

    Public Function InsertComment()

    Dim myComment As String
    Dim colnum As Integer
    Dim rownum As Integer
    Static ws2 As Worksheet

    Set ws2 = ActiveSheet
    rownum = Selection.row
    colnum = Selection.Column
    myComment = ""

    If Range(Application.Names("AppendComments").Value).V alue = True Then
    On Error Resume Next
    myComment = ActiveCell.Comment.Text ' Appends comment if true
    End If

    myComment = InputBox("Input Comment", "Add Comment", myComment)

    If Len(myComment) <> 0 Then
    ActiveCell.ClearComments
    ws2.Cells(rownum, colnum).AddComment.Text myComment
    End If

    End Function

    -------------------------------------------------------------------------------------------------

    One more bazaar thing. The cells I am attempting to add the comment to are formatted as NOT locked. However, if I select one of these NOT locked cells then double click on a locked cell I can add a comment TO THE NOT LOCKED CELL! Bazaar.

    Any suggestions?

  2. #2
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to add comments to a protected sheet (XP)

    Yes this is strange. I tried your code in Excel 2002 and was able to get it to add comments as you wanted but it does report the error message after the Worksheet_BeforeDoubleClick event fires. It works fine if the selected cell does not have a data validation list in it. I single stepped through the code and saw the comments being added just fine and then the sheet gets reprotected but right after the End Sub, the error message is displayed. Very weird and sounds like a bug in Excel. <img src=/S/disappointed.gif border=0 alt=disappointed width=15 height=15>

    Also, since Excel 2002 has more protection choices than previous versions, by default only the unlocked cells can be clicked by the user. Therefore, you should unlock the data validation cells. This causes another problem though since the user can't see the new cell comment since he can't get off the current cell w/o selecting another cell first. I suggest you set the position of the activecell after changing the comments so the user can at least click the data validation cell to see the comment.

    Instead of data validation, you could give the same 'look' with a combo box from the Forms toolbox. You provide a list of items and then give it a target cell to write the selected index. This index is the item # selected so if the list contains "aa, bb,cc,dd" and the user selected "cc" this target cell would be set to 3. Your event code would then get fired on the change of state of that cell.

    To add a comment, you could color the next cell over and document that these colored cells can contain comments by double-clicking them (and use you current code to add the comment). The combination of comments in a data validation cell is apparently not a good combination for Excel.

    See attached with modifications to your code and showing the combo box (it's not as slick as data validation but there's no other choice that I can think of). <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    Deb

  3. #3
    Lounger
    Join Date
    Jan 2003
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to add comments to a protected sheet (XP)

    Deb

    The lounge is a wonderful place. I am amazed at the quality and speed of the replies.

    Anyway, I read your reply two days ago but because it raised so many issues in my tiny brain I

Posting Permissions

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