Prevent Comments being added to unlocked cell in protected sheet - Excel 365

[SaraWitch]

Good afternoon all,

I'm using Excel 365, and I wonder if anyone knows if there's a way to prevent Comments being added to an unlocked cell in a protected sheet? When protecting the sheet, I have unticked 'Edit Objects' and this prevents Notes being added, but users are still able to add a Comment, which I don't want!

Also, I want users to be able to insert, cut and insert copied rows. Whilst I have 'Insert rows' and 'Delete rows' ticked when protecting the sheet, I am unable to do this. Any ideas please?

Ta muchly, folks!
Sara

 

[Logit]

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
    Sheet1.Range("A1").Comment.Delete
End Sub

 

[SaraWitch]

Hello Logit,

Thanks for the VBA - does this go on the relevant Sheet, ThisWorkbook or in a Module? (I've tried all three, and am still able to add a comment! Also need it to cover range F9:F91.)

I'm also still unable to insert, cut and paste rows in protected sheet, even though options ticked...

Thank you!

 

[Logit]

THe code goes in the Sheet Module.

Change A1 to F9:F91 ... see if it works.

 

[SaraWitch]

Hmmm. Tried that originally. Re-did it though, saved, closed and reopened the workbook to see if this made a difference. Sadly, doesn't seem to be working (I can still add a comment)...

 

[Fluff]

To my knowledge there is no way to prevent users from adding one of the New Comments, other than by locking the cell.
You could use VBA to delete all comments, but there is no way to detect if somebody added one, so the code would need to be run on a selection change event which could affect performance.

 

[SaraWitch]

Thank you, Fluff. Do you happen to know why the insert and delete row is not working when protecting the sheet (these are ticked, but unable to do this)?

 

[Logit]

Paste the following in the ThisWorkbook module :

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CommandBars("Cell").Controls("Insert Comment").Enabled = True
End Sub

Paste the following in the Sheet Module :

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("F9:F91").ClearComments
End Sub

 

[Logit]

Download example workbook : Comments Disable.xlsm

 

[Fluff]

Do you happen to know why the insert and delete row is not working when protecting the sheet

They won't work if any cell in the row is protected.