Unprotecting workbook

Unprotecting workbook

Didn't find your answer?

I have created a workbook & within each sheet some of the cells are proctected to prevent anyone typing figures into a formulated cell. When I want to change the workbook, I can only appear to unprotect each sheet one by one. As I have 32 sheets, this is very time consuming. If I select more than one sheet, the unprotect icon, is shaded out, so I cannot do it.

I have looked at the help articles, but just can't suss it.

Replies (3)

Please login or register to join the discussion.

avatar
By paulwakefield1
14th Sep 2012 09:35

There are two choices I think

One is the very excellent ASAP Utilities add in which allows quick and flexible protection and unprotection of multiple sheets. It also has many other useful utilities but costs $49 after the free trial.

The alternative is to write a small macro along the lines of:

Sub UnprotectWorkbook()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:=”xxxxxxxx”
Next ws
End Sub

Place it in ThisWorkbook in Visual Basic's Project Explorer.

Change "xxxxxxxx" to a password of your choice or remove Password:="xxxxxxxx" if the sheets do not need a password

Create another identical macro below End Sub and change Unprotect to Protect to protect all sheets.

In Excel 2007/2010, from the Developer tab (you may need to enable this first through File, Options, Customise ribbon and tick the Developer box in the right hand section), the Macros button will allow you to assign keyboard shortcuts to the macro. In Excel 2003, go through Tools, Macro, Macros.

Thanks (0)
avatar
By Cantona1
17th Sep 2012 13:52

The "Unprotect" should be

The "Unprotect" should be changed both on the sub name and the line "ws.Unprotect Password:=”xxxxxxxx.", otherwise the  second macro will not work if both codes have the same pro names.

 

Thanks (0)
avatar
By paulwakefield1
17th Sep 2012 14:08

Thanks

for clarifying that.My original post was ambiguous.

Thanks (0)