Macro: protect all sheets but allow Edit Objects

[Lauren Thomas]

Hello
I am trying to write a macro to lock a workbook that has multiple worksheets as it takes a lot of time to lock the worksheets manually.
I found this by googling and it seems to work well.

Sub protect_all_sheets()
top:
pass = InputBox("password?")
repass = InputBox("Verify Password")
If Not (pass = repass) Then
MsgBox "you made a boo boo"
GoTo top
End If
For i = 1 To Worksheets.Count
If Worksheets(i).ProtectContents = True Then GoTo oops
Next
For Each s In ActiveWorkbook.Worksheets
s.Protect Password:=pass
Next
Exit Sub
oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets then running this Macro."
End Sub

However I would also like users to be able to insert comments onto all of the worksheets so I am trying to edit the above code to include code that allows the use of 'Edit Objects'

Are you able to help?
Many thanks
Lauren


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[p45cal]

s.Protect Password:=pass, DrawingObjects:=False

 

[Lauren Thomas]

Hello
I am trying to write a macro to lock a workbook that has multiple worksheets as it takes a lot of time to lock the worksheets manually.
I found this by googling and it seems to work well.

Sub protect_all_sheets()
top:
pass = InputBox("password?")
repass = InputBox("Verify Password")
If Not (pass = repass) Then
MsgBox "you made a boo boo"
GoTo top
End If
For i = 1 To Worksheets.Count
If Worksheets(i).ProtectContents = True Then GoTo oops
Next
For Each s In ActiveWorkbook.Worksheets
s.Protect Password:=pass
Next
Exit Sub
oops: MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets then running this Macro."
End Sub

However I would also like users to be able to insert comments onto all of the worksheets so I am trying to edit the above code to include code that allows the use of 'Edit Objects'

Are you able to help?
Many thanks
Lauren


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

s.Protect Password:=pass, DrawingObjects:=False

I'm sorry, I have never actually written a macro before, where should I insert this into the above?

Also I have left some cells unlocked on one of the worksheets as I will need users to only be able to enter data into the unlocked cells. Would this still work if I protect the spreadsheet and not the worksheets?

Thanks in advance for your help
Lauren

 

[NARAYANK991]

Hi ,

Replace the code that you posted , with this :

Sub protect_all_sheets()
top:
    pass = InputBox("password?")
    repass = InputBox("Verify Password")
    If Not (pass = repass) Then
      MsgBox "you made a boo boo"
      GoTo top
    End If

    For i = 1 To Worksheets.Count
        If Worksheets(i).ProtectContents = True Then GoTo oops
    Next

    For Each s In ActiveWorkbook.Worksheets
        s.Protect Password:=pass, DrawingObjects:=False
    Next
    Exit Sub

oops:
    MsgBox "I think you have some sheets that are already protected. Please unprotect all sheets then run this Macro."
End Sub

This is for the first part of your problem , where you wanted users to be able to enter comments in a protected sheet.

Narayan

 

[NARAYANK991]

Hi ,

By spreadsheet I assume you mean workbook.

Protecting a workbook is different from protecting a worksheet ; protecting a workbook ensures users cannot insert / delete / rename worksheet tabs. Protecting a workbook does not prevent users from modifying cells within a workbook once they have opened the workbook.

Protecting a worksheet ensures that users cannot make changes within a worksheet , such as changing the contents of locked cells , inserting / deleting rows / columns ,...

Narayan

 

[Lauren Thomas]

Thank you, Thank you, Thank you - this works perfectly!
How do you get so smart???

Lauren

 

[NARAYANK991]

Thank you, Thank you, Thank you - this works perfectly!
How do you get so smart???

Lauren

Hi ,

Not smart ; more informed probably.

I have learnt a lot after I joined this forum , way back in 2011 ; 6 years of learning has helped me ; it should help anyone.

Narayan

 

[Lauren Thomas]

I feel like I am learning too - just very slowly!!!