Protecting Sheet vs. Workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 12 sheets in a workbook. I've locked the same cells in each sheet.
Once protected I enjoy the luxury of tabing only to the unlocked cells. I've
protected the entire workbook, however this feature doesn't work unless I
protect each sheet. Is there a way to protect all the sheets at the same
time? Thank you.
 
Skydiver, you can with a macro,

Sub Protect_All_Sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Protect password:="123"

Next ws

End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
 
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.

NOTE: you will probably wish to unprotect all sheets at some point.

Copy Paul's macro then change to.................

Sub UnProtect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="123"
Next ws
End Sub

And for just selected sheets...........

Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="123"
Next ws
End Sub


Gord Dibben MS Excel MVP

Paul, I'm sorry. I've never worked with macros before. I'm unfamiliar with
this process.
Click to expand...

Gord Dibben MS Excel MVP
 
Thanks Gord.

Gord Dibben said:
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to Tool>Macro>Macros.

You can also assign this macro to a button or a shortcut key combo.

NOTE: you will probably wish to unprotect all sheets at some point.

Copy Paul's macro then change to.................

Sub UnProtect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect Password:="123"
Next ws
End Sub

And for just selected sheets...........

Sub Protect_Selected_Sheets()
Set MySheets = ActiveWindow.SelectedSheets
For Each ws In MySheets
ws.Select
ws.Protect Password:="123"
Next ws
End Sub


Gord Dibben MS Excel MVP



Gord Dibben MS Excel MVP
Click to expand...
 
Back
Top