Protect all objects except comments

[taco truck]

Hi all,

I am new to the forum. My question is if I am designing a workbook for users, how do I allow a user to edit and insert comments but protect all other objects in a sheet (e.g. buttons, company logos that are images, etc.)? I know it is possible to uncheck a box in the protection settings that allows one to protect a sheet without protecting the objects. I also know that one can unlock an object so that when the sheet is protected (including objects) the object can still be edited. Again, my question is how do I allow a user to edit and insert comments but protect all other objects in a sheet (e.g. buttons, company logos that are images, etc.)?

Thanks!

 

[mikerickson]

Does the user have permission to edit cells' contents? Or are some cells locked?

 

[taco truck]

I have left some cells unlocked for the user to edit the contents. The user should also be allowed to insert and edit comments in the unlocked cells.

The protection settings are set to allow the user to select any cells, format cells, format rows, and format columns.

Again, I dont want to allow the user to edit objects, because there are objects other than comments that I don't want to be edited.

Thanks for you help.

Cheers,
TT

 

[mikerickson]

Assign this macro to all of the shapes on the sheet.

Sub AllShapeCode()
    With ActiveSheet
        If .ProtectContents Then
            .Unprotect
            .Protect DrawingObjects:=True, contents:=True, Scenarios:=True, userinterfaceonly:=True
        End If
    End With
End Sub

and put this in the sheet's code module

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    With ActiveSheet
        If .ProtectContents Then
            .Unprotect
            .Protect DrawingObjects:=False, contents:=True, Scenarios:=True, userinterfaceonly:=True
        End If
    End With
End Sub

When the sheet is protected, the user will be able to edit any cell's comments, but cannot alter any shape or object that calls that macro.

 

[taco truck]

OOOH! Very clever solution! Thank you! This works for almost all cases. I can still right click on the object in some cases and make edits. Any workarounds?

Thanks Again!