How to add comments to a protected sheet (XP)

Topic

New Reply

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).Value = 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?

Reply | Quote

Replies

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.

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).

Deb

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

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).

Deb

Reply | Quote