'Insert comment' facility in protected spreadsheets gets deactivated.

Question

When we protect a worksheet using password, insert comment option gets deactivated. Can we do something to use same in protected sheets too. Actually this option should also be made optional while saving a sheet like what we have asked for selecting unlocked cell,selecting locked cell, formatting cells,formatting column,formatting rows, and many others when we protect sheets using..tools-protection-protect worksheet.

Answer 1

I found that adding comments to cells using vba on a protected spreadsheet was a bit tricky (as the UserInterfaceOnly property of the protect method didn't seem to work with the .AddComment Method), perhaps one of the MVPs knows a way around this, in the meantime, here's my attempt:

Private Sub CommandButton1_Click()

'Unprotect the sheet using your password

Sheet1.Unprotect Password:="enteryourpasswordhere"

'Clear the comments on the target cell

' - this prevents errors when adding comments to a cell that already contains comments.

Range("A1").ClearComments

'Add a comment to the cell

Range("A1").AddComment ("This is my comment")

'Or as an alternative example you can use:

'Range("A1").AddComment (Range("A2").Value)

'Reprotect the sheet using your password

Sheet1.Protect Password:="enteryourpasswordhere"

End Sub

The big downside to this code is that it contains your sheet protection password, so you'd have to lock the VBA Project for viewing.

Essentially this code adds a comment to cell A1, the second option adds the value of cell A2 as a comment to cell A1.

Answer 2

Insert Comment is controlled by the Edit Objects checkbox in the Protect Sheet dialog's list.

Check that box and you will be able to insert comments (and also do things to other objects).

There is no options specifically aimed at comments.  If you don't want the full effect of Edit Objects you would need to write a macro to set the comment up for you.

---

Bill Manville. Excel MVP, Oxford, England. www.manville.org.uk

Answer 3

.....then can I ve that particular macro please for Insert comment only .

Use the macro recorder to 1) unprotect the worksheet, 2) insert a comment, and 3) re-protect the worksheet.  You can then modify it to work with the activecell or share it here and someone can show you how to modify it.

---

Tushar Mehta (Technology and Operations Consulting)

www.tushar-mehta.com (Excel and PowerPoint add-ins and tutorials)

Microsoft MVP Excel 2000-Present

Answer 4

Thx but its not useful.........

Answer 5

.....then can I ve that particular macro please for Insert comment only .