Excel 2003 - Prevent Comments in protected locked cells

[frank_m]

In Excel 2003 - Is there any way of Preventing Comments in protected locked cells? (other than removing the command from the menu)

Thanks

[frank_m]

Hi Bob, I modified some similar code that you wrote for me awhile back.

It seems to work fine.

anything you, or anyone here, would do to make my hack of your code better?

Edit: I added a msgbox informing the user that cell comments are not allowed.

Edit# 2 I do see a minor issue where if the user adds a comment then saves and closes the workbook, Excel has no way to remember the previous cell. - I can live with that though.

Private prevCol As Integer
Private prevRow As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim Rng As Range
On Error Resume Next
Set Rng = ActiveSheet.Cells(prevRow, prevCol)
If Not (Rng.Comment Is Nothing) Then
MsgBox "Cell comments are not allowed. - It will now be deleted automatically"
Rng.Comment.Delete
End If

prevCol = Target.Column
prevRow = Target.Row
End Sub

[frank_m]

Useing the code below in the ThisWorkbook Open Event seems to cover one issue.

The only slight bug that I see now is with the selection change event code I occasionaly get the msgbox poping up twice. (Once when I make a new selection and the Comment gets deleted and again when I make second selection when there are no comments)

Private Sub Workbook_Open()
Dim Rng1 As Range
Set Rng1 = Sheets("Sheet1").Cells(ActiveCell.Row, ActiveCell.Column)
If Not (Rng1.Comment Is Nothing) Then
MsgBox "Cell comments are not allowed. - It will now be deleted automatically"
Rng1.Comment.Delete
End If
End Sub