Passwork Protect Multiple Sheets at once with VBA and allow edit objects

[jgrove15]

I am using the following VBA code to password lock multiple sheets in my excel spread sheet.

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

This works great but I also want to allow the users to insert comments. This is easily done by checking the edit objects box if I am password protecting one sheet at a time but I don't know if I can add it to this code to do this to all worksheets that I am password protecting. I am very new to VBA and found this code on line which has been a big help.

Thx in advance.

Josh

 

[Fluff]

Hi & welcome to MrExcel.
If you unprotect a sheet & then start the macro recorder & record yourself protecting the sheet with the options you want.
You will get the code you need, which you can then add to your existing code.
If you have any problems, let us know

 

[jgrove15]

I ran the recorder and get the following code:

Sub Macro3()
'
' Macro3 Macro
'
'
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
True
End Sub

I am not certain where to add it in my existing code and also it seems like it will only be for the active sheet and not all sheets. As I said I am new to VBA so all help is greatly appreciated.

 

[Fluff]

Replace this line

s.Protect Password:=pass

in your code with

s.Protect Password:=pass, DrawingObjects:=False, Contents:=True, Scenarios:= True

 

[jgrove15]

Fantastic, thanks Fluff. That works great and is exactly what I needed.

 

[Fluff]

Glad to help & thanks for the feedback