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

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

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

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

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

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" wrote in message
...
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!!

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$5 8,$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$4 58
__________________________________________________ ____________________

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$5 2:$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$3 20,$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


"Paul B" wrote:

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" wrote in message
...
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!!