Protecting Sheet vs. Workbook

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

 

[Paul B]

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

 

[Guest]

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

 

[Gord Dibben]

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.

Gord Dibben MS Excel MVP

 

[Guest]

Thanks Gord.

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