Unprotect multiple worksheets at the same time

[juye]

I am wondering if unprotecting mulitple sheets at the same time is possible? When I select all sheets, the unprotect sheet is grayed out.

 

[Paul Sheppard]

I am wondering if unprotecting mulitple sheets at the same time is possible? When I select all sheets, the unprotect sheet is grayed out.

Hi juye

You can do this with code as follows:

Public Sub Unprotect_All()
Dim wks As Worksheet
Dim vPword As Variant
On Error Resume Next
For Each wks In ActiveWorkbook.Worksheets
With wks
.Unprotect vPword
Do While .ProtectContents
vPword = Application.InputBox( _
prompt:="Enter password for " & .Name, _
Title:="Unprotect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
.Unprotect vPword
Loop
End With
Next
End Sub

And you can protect all sheets with this code

Public Sub Protect_All()
Dim wks As Worksheet
Dim vPword As Variant
vPword = Application.InputBox( _
prompt:="Enter Password: ", _
Title:="Protect sheets", _
Default:="", _
Type:=2)
If vPword = False Then Exit Sub 'user cancelled
For Each wks In ActiveWorkbook.Worksheets
wks.Protect vPword
Next
End Sub

Hope this helps

Paul

 

[Kim B]

Hi. This works great for me too except I want my users to be able to autofit columns and rows. But the macro to protect protects all functions. Any ideas?

Thanks

 

[Smitty]

Hi. This works great for me too except I want my users to be able to autofit columns and rows. But the macro to protect protects all functions. Any ideas?

Thanks

In Excel 2003 on you have that option in the Protection dialog. Otherwise you can code for it and assign it to a button.

HTH,

 

[Kim B]

But when I run the macro to reprotect, it protects everything. So is there a way to tell the macro not to protect certain cells on certain sheets. The unprotected cells are the same for certain sheets but different for others.

Thanks as always

 

[Smitty]

You need to select the cells and unlock them manually (Format-->Cells-->Protection-->Uncheck "Locked") before running the code.

Otherwise you need to do it in the code, which means you'll need to apply several scenarios. It's not that hard to do, but it is generally easier if you set it up outside of the code first instead of doing the same thing repeatedly, when it only needs to be done once.

 

[Kim B]

But when I run the macro to reprotect, it protects everything. So is there a way to tell the macro not to protect certain cells on certain sheets. The unprotected cells are the same for certain sheets but different for others.

Thanks as always

 

[Smitty]

OK, by default all cells in a worksheet are set to Locked, which means that you can't interact with them when the sheet is protected. So before you apply sheet protection you need to select the cells you want to be editable and unlock them (see above - the Locked property is what's important here).

As I mentioned, you can do this in your code, but unless it's a variable range, it's much more efficient to unlock those cells manually before applying protection.

 

[claremark]

Is it possible to have this work(unprotect/protect) on multiple workbooks that are open?

I have 10 workbooks that have multiple worksheets. All have the same password.

Thanks Claremark