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

jgrove15

New Member
Joined
Aug 3, 2018
Messages
3
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,930
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 3, 2018
Messages
3
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,930
Office Version
  1. 365
Platform
  1. Windows
Replace this line
Code:
s.Protect Password:=pass
in your code with
Code:
s.Protect Password:=pass, DrawingObjects:=False, Contents:=True, Scenarios:= True
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,930
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Forum statistics

Threads
1,136,341
Messages
5,675,206
Members
419,553
Latest member
hanahass

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top