Unprotect multiple worksheets at the same time

juye

New Member
Joined
Apr 8, 2003
Messages
5
I am wondering if unprotecting mulitple sheets at the same time is possible? When I select all sheets, the unprotect sheet is grayed out.
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Paul Sheppard

New Member
Joined
Jul 24, 2006
Messages
45
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:

Code:
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

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
 
Last edited by a moderator:

Kim B

Board Regular
Joined
Jun 16, 2008
Messages
182
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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Jun 16, 2008
Messages
182

ADVERTISEMENT

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

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Jun 16, 2008
Messages
182

ADVERTISEMENT

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

Legend
Joined
May 15, 2003
Messages
29,536
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

New Member
Joined
Jun 23, 2008
Messages
18
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,719
Messages
5,597,734
Members
414,170
Latest member
Mdm

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