comment based on cell value in protected sheet

[anilkut]

I used following vba for comment based on cell value, but when sheet is protected its not working (message .. Run time error '91': prompting for debug)

Sub addCommnent()
''that cell is A1
Dim commentTxt As String, commentRng As Range
Set commentRng = Range("B4")
commentTxt = Range("M15").Value
On Error Resume Next

With commentRng
.Comment.Delete
.AddComment commentTxt

End With
End Sub
Sub addCommtent()
''that cell is A2
Dim commentTxt1 As String, commentRng1 As Range
Set commentRng1 = Range("B5")
commentTxt1 = Range("N15").Value
On Error Resume Next

With commentRng1
.Comment.Delete
.AddComment commentTxt1

End With
End Sub

Private Sub Worksheet_Calculate()
If Range("B4").Comment.Text <> Range("M15").Value Then addCommnent
If Range("B5").Comment.Text <> Range("N15").Value Then addCommtent
End Sub


please guide where I am wrong

 

[Keebellah]

Check what is protected when you protect the worksheet.
You can also unprotect it when the macro runs and protect it again after completion

When protecting a worksheet using a macro you can add the parameter UserInterFaceOnly:=True, this will allow to modify when using VBA even if the sheet is protected but not the suer