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

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
104
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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,056
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
104
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,056
THe code goes in the Sheet Module.

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

SaraWitch

Board Regular
Joined
Sep 29, 2015
Messages
104
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
58,135
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
104
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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,056
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: 2

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
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

Watch MrExcel Video

Forum statistics

Threads
1,132,640
Messages
5,654,541
Members
418,139
Latest member
nimesh72

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