Protection of identical cells on multiple worksheets

shawnlacey · Apr 28, 2006

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

Paul B · Apr 29, 2006

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 · May 1, 2006

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$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) to be the only fields allowed to be edited?

Thanks,
Shawn

Paul B · May 2, 2006

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 said:
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$51,$C$53:$I$106,$C$108:$I$1
35,$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) to be the only fields allowed to be edited?

shawnlacey · May 3, 2006

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

Paul B said:
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

(=$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$1
35,$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) to be the only fields allowed to be edited?

Paul B · May 4, 2006

Your welcome

--
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 · May 16, 2006

Hi again Paul B,

Can use your help again. The direct scope of the workbook I'm working on
has changed. The work book has a total of 42 pages. The first sheet is more
or less a report based on the second sheet which is a summary of the
remaining 40 sheets which are identical. I was working with the code you
provided me but needless to say I've been pulling my hair out.

I want to lock the whole workbook and allow certain cells to be edited. The
problem I have is the first two pages have different cells I want the users
to be able to edit while the last 40 are all identical.

Here is what I put together. Hope you can help..

The code you submitted:

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:="1234"
Next ws
End Sub

NEW PROBLEM: Lock all sheets with these exceptions.

Bottom Line Protection (Sheet 1) Range of Cells:

=$G$1,$B$5,$D$5,$B$45:$G$46,$A$48:$G$57,$B$58:$G$58,$A$63:$G$72,$A$78:$A$80,$D$83:$E$83,$A$85,$D$86:$E$86,$A$88,$D$89:$E$89,$A$91,$D$93:$E$93,$A$95,$B$97,$D$99:$E$99,$A$102
______________________________________________________________________

Project Manhour Sumary (Sheet 2) Range of Cells:

=$G$8:$G$15,$G$17:$G$32,$G$34,$G$37,$G$40,$G$43,$G$46,$G$49,$G$52,$G$55,$G$58:$G$157,$G$159:$G$210,$G$212:$G$311,$G$313,$G$316,$G$319,$G$322:$G$337,$G$339:$G$350,$G$352:$G$363,$G$365:$G$390,$G$392:$G$417,$G$419:$G$444,$G$446,$G$449,$G$452,$G$455,$G$458
______________________________________________________________________

WE Apr 24 - WE Jan 1 ( Weeks Ending Sheet 3 thru Sheet 40) or (We Apr 24,WE
May 1,We May 8,....etc..)

Range of Cells:

Range 1:

=$C$8:$I$15,$C$17:$I$32,$C$34:$I$35,$C$37:$I$38,$C$40:$I$41,$C$43:$I$44,$C$46:$I$47,$C$49:$I$50,$C$52:$I$53,$C$55:$I$56,$C$58:$I$157,$C$159:$I$210,$C$212:$I$311,$C$313:$I$314,$C$316:$I$317,$C$319:$I$320,$C$322:$I$337,$C$339:$I$350,$C$352:$I$363

Range 2:

=$C$446:$I$447,$C$449:$I$450,$C$452:$I$453,$C$455:$I$456,$C$458:$I$459,$C$461:$I$466

I had to create 2 sets of ranges because trying to include them all in one
set would keep blowing up.

I'd appreceiate any help you can give me.

As always Thanks,

Shawn