Sheet protection on multiple sheets

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Good Morning All,

Using Excel XP.

I have a workbook with 100 worksheets. On all of the sheets I have certain
cells that are protected. Is there any way that I can protect the sheets
all at once? Right now I have to go to each individual sheet to protect it.
Thank you in advance.

Mike
 
If you protect the sheets with a common password, you could use a little macro:

Option Explicit
Sub testme()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Protect Password:="samepasswordhere"
Next wks
End Sub

If you're new to macros, you may want to read David's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Mike, you can with VBA, like this

Sub protect_sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Protect password:="123"

Next ws

End Sub



Sub unprotect_sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Unprotect password:="123"

Next ws

End Sub



To put in this macro, from your workbook right-click the workbook's icon and
pick View Code. This icon is to the left of the "File" menu this will open
the VBA editor, in the left hand window click on your workbook name, go to
insert, module, and paste the code in the window that opens on the right
hand side, press Alt and Q to close this window and go back to your workbook
and press alt and F8, this will bring up a box to pick the Macro from, click
on the Macro name to run it. If you are using excel 2000 or newer you may
have to change the macro security settings to get the macro to run. To
change the security settings go to tools, macro, security, security level
and set it to medium


--
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
 
thanks guys for your help, works great!!
Paul B said:
Mike, you can with VBA, like this

Sub protect_sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Protect password:="123"

Next ws

End Sub



Sub unprotect_sheets()

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

ws.Unprotect password:="123"

Next ws

End Sub



To put in this macro, from your workbook right-click the workbook's icon
and pick View Code. This icon is to the left of the "File" menu this will
open the VBA editor, in the left hand window click on your workbook name,
go to insert, module, and paste the code in the window that opens on the
right hand side, press Alt and Q to close this window and go back to your
workbook and press alt and F8, this will bring up a box to pick the Macro
from, click on the Macro name to run it. If you are using excel 2000 or
newer you may have to change the macro security settings to get the macro
to run. To change the security settings go to tools, macro, security,
security level and set it to medium


--
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
Click to expand...
 
Hi

just a quick note, this does not work when protecting sheets ... a vba
solution is the only option.
 
Back
Top