Adding comments into a protected worksheet

[Sean]

Hi all,

I have a workbook that is used for estimating, Which i have protected
by a password. I'm trying to allow the user to be able to insert
comments and am unable to do so when it is protected. I am currently
using this macro:


Private Sub Workbook_Open()
With Worksheets("Building 1")
..Protect Password:="12345", userinterfaceonly:=True
..EnableOutlining = True
End With


To allow the user to use the outline function; the +/- feature. I know

you should be able to go to tool->protection--> and check edit objects
to allow the user to insert comments, it works while i have the
workbook open, but as soon as i close it an open the file again, i can
no longer insert comments. Does anyone have a macro that will allow a
user to inser a comment cells that are only unlocked?


Thank you,


Sean

 

[Dave Peterson]

If you do the protection manually, you'll see an option for "edit Objects".

You can add a parm to your .protect line: DrawingObjects:=False
to do it in code.

 

[Guest]

But how come I add
DrawingObjects = False to my macro, it still doesn't work for text box
protection. It works when I manual uncheck "objects" in protection dialog.
I have so many similar worksheets to be protected, I hope someone can give
me a hint to do them automatically!
Belowed is the macro I use.

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
DrawingObjects = False
Sheets(n).Protect password:="123"

Next n
Application.ScreenUpdating = True
End Sub

 

[Dave Peterson]

Option Explicit
Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Protect Password:="123", DrawingObjects:=False
Next wks
Application.ScreenUpdating = True
End Sub

 

[Guest]

Please disregard my question. I figure it out. Below is new macro and it
works!!

Sub ProtectAllSheets()
Application.ScreenUpdating = False
Dim n As Single
For n = 1 To Sheets.Count
DrawingObjects = False
Sheets(n).Protect password:="123", DrawingObjects:=False,
Contents:=True, Scenarios:=True
Next n
Application.ScreenUpdating = True
End Sub

 

[Guest]

Thanks Dave.
I get several good hints from your previous posts about EXCEL. You are a
very kind person. Thanks a lot!

 

[Dave Peterson]

If you used this version, then you can delete this line:

DrawingObjects = False

not the "DrawingObjects:=False", though.