ExcelBanter - View Single Post - Protection of identical cells on multiple worksheets

Thanks, Paul.....Your the man!!!

"Paul B" wrote:

Shawn, I thought you had the cells unlocked and only needed to protect the
sheets all at once, try this to unlock the cells you want and protect the
sheets

Sub Protect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
'unlock the cells below
ws.Range("$A$1,$A$2,$A$3,$C$8:$I$27,$C$29:$I$38" & _
",$C$40:$I$51,$C$53:$I$106,$C$108:$I$135" & _
",$C$137:$I$198,$C$200:$I$215,$C$217:$I$228" & _
",$C$230:$I$251,$C$253:$I$280,$C$282:$I$289" & _
",$C$291:$I$294").Locked = False

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

"shawnlacey" wrote in message
...
Paul B,

Thanks, that allowed me to lock all sheets entirely but that also include
the cells I need to be unlocked. How do I code the macro to allow

(=$A$1,$A$2,$A$3,$C$8:$I$27,$C$29:$I$38,$C$40:$I$5 1,$C$53:$I$106,$C$108:$I$1
35,$C$137:$I$198,$C$200:$I$215,$C$217:$I$228,$C$23 0:$I$251,$C$253:$I$280,$C$
282:$I$289,$C$291:$I$294) to be the only fields allowed to be edited?

Thanks,
Shawn

"Paul B" wrote:

shawnlacey, you could use a macro like this

Sub Protect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect password:="123"
Next ws
End Sub

and to unprotect

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

And if you are new to macros, 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 Project Explorer
click
on your workbook name, if you don't see it press CTRL + r to open the
Project Explorer, then 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

you may also what to have a look here on getting started with macros
http://www.mvps.org/dmcritchie/excel/getstarted.htm

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

"shawnlacey" wrote in message
...
I have a weekly project report in Excell that needs to be filled out
on a
weekly basis till the end of the year. Each sheet is the same and I
only
want the user to be able to fill in the exact same cells each week and
lock
down the rest of the sheet to protect the formulas. Is there a way to
all
the sheets as opposed to having to lock each one individually??

Help Please!!