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

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
153
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
:)
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,573
VBA Code:
Option Explicit

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

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
153
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Aug 31, 2016
Messages
4,573
THe code goes in the Sheet Module.

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

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
153
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,598
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Sep 29, 2015
Messages
153
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Aug 31, 2016
Messages
4,573
Paste the following in the ThisWorkbook module :

VBA Code:
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 :

Code:
Option Explicit

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

Attachments

  • Comments.jpg
    Comments.jpg
    49.8 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
79,598
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

Forum statistics

Threads
1,181,177
Messages
5,928,550
Members
436,607
Latest member
MMCP15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top