Copy protection from one worksheet to another

J

Johnell

Hi,

I have multiple worksheets in one workbook that I need to add protection to.
All the worksheets require the same protection with the same password. The
protection consists of 2 sets of "allow users to edit range" and overall
worksheet protection.

I have 2 questions:

1. Is there a way to copy the protection from one worksheet to another, or
create the protection to all worksheets via a macro, so I don't have to
protect each worksheet separately?

2. Is there way to unprotect all the worksheets with same password at one
time via a macro or other mechanism?

Thanks,

Johnell
 
Joined
Jun 4, 2008
Messages
15
Reaction score
0
Sub p()
Dim sh As Worksheet
For Each sh In Sheets
sh.Protect Password:=123, DrawingObjects:=False, Contents:=True, Scenarios:= _
True
Next
End Sub

Sub unp()
Dim sh As Worksheet
For Each sh In Sheets
sh.Unprotect 123
Next
End Sub
 
S

Spiky

Hi,

I have multiple worksheets in one workbook that I need to add protection to.
All the worksheets require the same protection with the same password. The
protection consists of 2 sets of "allow users to edit range" and overall
worksheet protection.

I have 2 questions:

1. Is there a way to copy the protection from one worksheet to another, or
create the protection to all worksheets via a macro, so I don't have to
protect each worksheet separately?

2. Is there way to unprotect all the worksheets with same password at one
time via a macro or other mechanism?

Thanks,

Johnell

1. I don't think so.
2. I set up macros to do both lock/unlock and assigned them to toolbar
buttons so they are easy to use. Here's one, change "Unprotect" to
"Protect" for the 2nd. I was trying to get this to work on multiple
selected sheets, but this one only seems to work on one at a time.
There should be a way...it works with other commands, just not
"Protect". Maybe someone else can help both of us.

Sub Unlock_Sheets()
Dim x As Worksheet
For Each x In ActiveWindow.SelectedSheets
x.Unprotect Password:="xxxx"
Next x
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top