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.
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.
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.