software

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:=quot;123quot;
Next ws
End Sub

and to unprotect

Sub Unprotect_All_Sheets()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Unprotect password:=quot;123quot;
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 quot;Filequot; 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
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 amp; 2003

quot;shawnlaceyquot; gt; wrote in message
...
gt; I have a weekly project report in Excell that needs to be filled out on a
gt; weekly basis till the end of the year. Each sheet is the same and I only
gt; want the user to be able to fill in the exact same cells each week and
lock
gt; down the rest of the sheet to protect the formulas. Is there a way to all
gt; the sheets as opposed to having to lock each one individually??
gt;
gt; 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

quot;Paul Bquot; wrote:

gt; shawnlacey, you could use a macro like this
gt;
gt; Sub Protect_All_Sheets()
gt; Dim ws As Worksheet
gt; For Each ws In ThisWorkbook.Worksheets
gt; ws.Protect password:=quot;123quot;
gt; Next ws
gt; End Sub
gt;
gt; and to unprotect
gt;
gt; Sub Unprotect_All_Sheets()
gt; Dim ws As Worksheet
gt; For Each ws In ThisWorkbook.Worksheets
gt; ws.Unprotect password:=quot;123quot;
gt; Next ws
gt; End Sub
gt;
gt; And if you are new to macros, to put in this macro, from your workbook
gt; right-click the workbook's icon and pick View Code. This icon is to the left
gt; of the quot;Filequot; menu this will open the VBA editor, in Project Explorer click
gt; on your workbook name, if you don't see it press CTRL r to open the
gt; Project Explorer, then go to insert, module, and paste the code in the
gt; window that opens on the right hand side, press Alt and Q to close this
gt; window and go back to your workbook and press alt and F8, this will bring
gt; up a box to pick the Macro from, click on the Macro name to run it. If you
gt; are using excel 2000 or newer you may have to change the macro security
gt; settings to get the macro to run. To change the security settings go to
gt; tools, macro, security, security level and set it to medium
gt;
gt; you may also what to have a look here on getting started with macros
gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt;
gt;
gt;
gt; --
gt; Paul B
gt; Always backup your data before trying something new
gt; Please post any response to the newsgroups so others can benefit from it
gt; Feedback on answers is always appreciated!
gt; Using Excel 2002 amp; 2003
gt;
gt; quot;shawnlaceyquot; gt; wrote in message
gt; ...
gt; gt; I have a weekly project report in Excell that needs to be filled out on a
gt; gt; weekly basis till the end of the year. Each sheet is the same and I only
gt; gt; want the user to be able to fill in the exact same cells each week and
gt; lock
gt; gt; down the rest of the sheet to protect the formulas. Is there a way to all
gt; gt; the sheets as opposed to having to lock each one individually??
gt; gt;
gt; gt; Help Please!!
gt;
gt;
gt;

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(quot;$A$1,$A$2,$A$3,$C$8:$I$27,$C$29:$I$38quot; amp; _
quot;,$C$40:$I$51,$C$53:$I$106,$C$108:$I$135quot; amp; _
quot;,$C$137:$I$198,$C$200:$I$215,$C$217:$I$228quot; amp; _
quot;,$C$230:$I$251,$C$253:$I$280,$C$282:$I$289quot; amp; _
quot;,$C$291:$I$294quot;).Locked = False

ws.Protect Password:=quot;123quot;
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 amp; 2003

quot;shawnlaceyquot; gt; wrote in message
...
gt; Paul B,
gt;
gt; Thanks, that allowed me to lock all sheets entirely but that also include
gt; the cells I need to be unlocked. How do I code the macro to allow
gt;
(=$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?
gt;
gt; Thanks,
gt; Shawn
gt;
gt;
gt;
gt;
gt; quot;Paul Bquot; wrote:
gt;
gt; gt; shawnlacey, you could use a macro like this
gt; gt;
gt; gt; Sub Protect_All_Sheets()
gt; gt; Dim ws As Worksheet
gt; gt; For Each ws In ThisWorkbook.Worksheets
gt; gt; ws.Protect password:=quot;123quot;
gt; gt; Next ws
gt; gt; End Sub
gt; gt;
gt; gt; and to unprotect
gt; gt;
gt; gt; Sub Unprotect_All_Sheets()
gt; gt; Dim ws As Worksheet
gt; gt; For Each ws In ThisWorkbook.Worksheets
gt; gt; ws.Unprotect password:=quot;123quot;
gt; gt; Next ws
gt; gt; End Sub
gt; gt;
gt; gt; And if you are new to macros, to put in this macro, from your workbook
gt; gt; right-click the workbook's icon and pick View Code. This icon is to the
left
gt; gt; of the quot;Filequot; menu this will open the VBA editor, in Project Explorer
click
gt; gt; on your workbook name, if you don't see it press CTRL r to open the
gt; gt; Project Explorer, then go to insert, module, and paste the code in the
gt; gt; window that opens on the right hand side, press Alt and Q to close this
gt; gt; window and go back to your workbook and press alt and F8, this will
bring
gt; gt; up a box to pick the Macro from, click on the Macro name to run it. If
you
gt; gt; are using excel 2000 or newer you may have to change the macro security
gt; gt; settings to get the macro to run. To change the security settings go to
gt; gt; tools, macro, security, security level and set it to medium
gt; gt;
gt; gt; you may also what to have a look here on getting started with macros
gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt;
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Paul B
gt; gt; Always backup your data before trying something new
gt; gt; Please post any response to the newsgroups so others can benefit from it
gt; gt; Feedback on answers is always appreciated!
gt; gt; Using Excel 2002 amp; 2003
gt; gt;
gt; gt; quot;shawnlaceyquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; I have a weekly project report in Excell that needs to be filled out
on a
gt; gt; gt; weekly basis till the end of the year. Each sheet is the same and I
only
gt; gt; gt; want the user to be able to fill in the exact same cells each week and
gt; gt; lock
gt; gt; gt; down the rest of the sheet to protect the formulas. Is there a way to
all
gt; gt; gt; the sheets as opposed to having to lock each one individually??
gt; gt; gt;
gt; gt; gt; Help Please!!
gt; gt;
gt; gt;
gt; gt;
Thanks, Paul.....Your the man!!!

quot;Paul Bquot; wrote:

gt; Shawn, I thought you had the cells unlocked and only needed to protect the
gt; sheets all at once, try this to unlock the cells you want and protect the
gt; sheets
gt;
gt; Sub Protect_All_Sheets()
gt; Dim ws As Worksheet
gt; For Each ws In ThisWorkbook.Worksheets
gt; 'unlock the cells below
gt; ws.Range(quot;$A$1,$A$2,$A$3,$C$8:$I$27,$C$29:$I$38quot; amp; _
gt; quot;,$C$40:$I$51,$C$53:$I$106,$C$108:$I$135quot; amp; _
gt; quot;,$C$137:$I$198,$C$200:$I$215,$C$217:$I$228quot; amp; _
gt; quot;,$C$230:$I$251,$C$253:$I$280,$C$282:$I$289quot; amp; _
gt; quot;,$C$291:$I$294quot;).Locked = False
gt;
gt; ws.Protect Password:=quot;123quot;
gt; Next ws
gt; End Sub
gt;
gt;
gt; --
gt; Paul B
gt; Always backup your data before trying something new
gt; Please post any response to the newsgroups so others can benefit from it
gt; Feedback on answers is always appreciated!
gt; Using Excel 2002 amp; 2003
gt;
gt; quot;shawnlaceyquot; gt; wrote in message
gt; ...
gt; gt; Paul B,
gt; gt;
gt; gt; Thanks, that allowed me to lock all sheets entirely but that also include
gt; gt; the cells I need to be unlocked. How do I code the macro to allow
gt; gt;
gt; (=$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
gt; 35,$C$137:$I$198,$C$200:$I$215,$C$217:$I$228,$C$23 0:$I$251,$C$253:$I$280,$C$
gt; 282:$I$289,$C$291:$I$294) to be the only fields allowed to be edited?
gt; gt;
gt; gt; Thanks,
gt; gt; Shawn
gt; gt;
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Paul Bquot; wrote:
gt; gt;
gt; gt; gt; shawnlacey, you could use a macro like this
gt; gt; gt;
gt; gt; gt; Sub Protect_All_Sheets()
gt; gt; gt; Dim ws As Worksheet
gt; gt; gt; For Each ws In ThisWorkbook.Worksheets
gt; gt; gt; ws.Protect password:=quot;123quot;
gt; gt; gt; Next ws
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; and to unprotect
gt; gt; gt;
gt; gt; gt; Sub Unprotect_All_Sheets()
gt; gt; gt; Dim ws As Worksheet
gt; gt; gt; For Each ws In ThisWorkbook.Worksheets
gt; gt; gt; ws.Unprotect password:=quot;123quot;
gt; gt; gt; Next ws
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt; And if you are new to macros, to put in this macro, from your workbook
gt; gt; gt; right-click the workbook's icon and pick View Code. This icon is to the
gt; left
gt; gt; gt; of the quot;Filequot; menu this will open the VBA editor, in Project Explorer
gt; click
gt; gt; gt; on your workbook name, if you don't see it press CTRL r to open the
gt; gt; gt; Project Explorer, then go to insert, module, and paste the code in the
gt; gt; gt; window that opens on the right hand side, press Alt and Q to close this
gt; gt; gt; window and go back to your workbook and press alt and F8, this will
gt; bring
gt; gt; gt; up a box to pick the Macro from, click on the Macro name to run it. If
gt; you
gt; gt; gt; are using excel 2000 or newer you may have to change the macro security
gt; gt; gt; settings to get the macro to run. To change the security settings go to
gt; gt; gt; tools, macro, security, security level and set it to medium
gt; gt; gt;
gt; gt; gt; you may also what to have a look here on getting started with macros
gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Paul B
gt; gt; gt; Always backup your data before trying something new
gt; gt; gt; Please post any response to the newsgroups so others can benefit from it
gt; gt; gt; Feedback on answers is always appreciated!
gt; gt; gt; Using Excel 2002 amp; 2003
gt; gt; gt;
gt; gt; gt; quot;shawnlaceyquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; I have a weekly project report in Excell that needs to be filled out
gt; on a
gt; gt; gt; gt; weekly basis till the end of the year. Each sheet is the same and I
gt; only
gt; gt; gt; gt; want the user to be able to fill in the exact same cells each week and
gt; gt; gt; lock
gt; gt; gt; gt; down the rest of the sheet to protect the formulas. Is there a way to
gt; all
gt; gt; gt; gt; the sheets as opposed to having to lock each one individually??
gt; gt; gt; gt;
gt; gt; gt; gt; Help Please!!
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;

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 amp; 2003

quot;shawnlaceyquot; gt; wrote in message
...
gt; Thanks, Paul.....Your the man!!!
gt;
gt; quot;Paul Bquot; wrote:
gt;
gt; gt; Shawn, I thought you had the cells unlocked and only needed to protect
the
gt; gt; sheets all at once, try this to unlock the cells you want and protect
the
gt; gt; sheets
gt; gt;
gt; gt; Sub Protect_All_Sheets()
gt; gt; Dim ws As Worksheet
gt; gt; For Each ws In ThisWorkbook.Worksheets
gt; gt; 'unlock the cells below
gt; gt; ws.Range(quot;$A$1,$A$2,$A$3,$C$8:$I$27,$C$29:$I$38quot; amp; _
gt; gt; quot;,$C$40:$I$51,$C$53:$I$106,$C$108:$I$135quot; amp; _
gt; gt; quot;,$C$137:$I$198,$C$200:$I$215,$C$217:$I$228quot; amp; _
gt; gt; quot;,$C$230:$I$251,$C$253:$I$280,$C$282:$I$289quot; amp; _
gt; gt; quot;,$C$291:$I$294quot;).Locked = False
gt; gt;
gt; gt; ws.Protect Password:=quot;123quot;
gt; gt; Next ws
gt; gt; End Sub
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Paul B
gt; gt; Always backup your data before trying something new
gt; gt; Please post any response to the newsgroups so others can benefit from it
gt; gt; Feedback on answers is always appreciated!
gt; gt; Using Excel 2002 amp; 2003
gt; gt;
gt; gt; quot;shawnlaceyquot; gt; wrote in message
gt; gt; ...
gt; gt; gt; Paul B,
gt; gt; gt;
gt; gt; gt; Thanks, that allowed me to lock all sheets entirely but that also
include
gt; gt; gt; the cells I need to be unlocked. How do I code the macro to allow
gt; gt; gt;
gt; gt;
(=$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
gt; gt;
35,$C$137:$I$198,$C$200:$I$215,$C$217:$I$228,$C$23 0:$I$251,$C$253:$I$280,$C$
gt; gt; 282:$I$289,$C$291:$I$294) to be the only fields allowed to be edited?
gt; gt; gt;
gt; gt; gt; Thanks,
gt; gt; gt; Shawn
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Paul Bquot; wrote:
gt; gt; gt;
gt; gt; gt; gt; shawnlacey, you could use a macro like this
gt; gt; gt; gt;
gt; gt; gt; gt; Sub Protect_All_Sheets()
gt; gt; gt; gt; Dim ws As Worksheet
gt; gt; gt; gt; For Each ws In ThisWorkbook.Worksheets
gt; gt; gt; gt; ws.Protect password:=quot;123quot;
gt; gt; gt; gt; Next ws
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; and to unprotect
gt; gt; gt; gt;
gt; gt; gt; gt; Sub Unprotect_All_Sheets()
gt; gt; gt; gt; Dim ws As Worksheet
gt; gt; gt; gt; For Each ws In ThisWorkbook.Worksheets
gt; gt; gt; gt; ws.Unprotect password:=quot;123quot;
gt; gt; gt; gt; Next ws
gt; gt; gt; gt; End Sub
gt; gt; gt; gt;
gt; gt; gt; gt; And if you are new to macros, to put in this macro, from your
workbook
gt; gt; gt; gt; right-click the workbook's icon and pick View Code. This icon is to
the
gt; gt; left
gt; gt; gt; gt; of the quot;Filequot; menu this will open the VBA editor, in Project
Explorer
gt; gt; click
gt; gt; gt; gt; on your workbook name, if you don't see it press CTRL r to open
the
gt; gt; gt; gt; Project Explorer, then go to insert, module, and paste the code in
the
gt; gt; gt; gt; window that opens on the right hand side, press Alt and Q to close
this
gt; gt; gt; gt; window and go back to your workbook and press alt and F8, this will
gt; gt; bring
gt; gt; gt; gt; up a box to pick the Macro from, click on the Macro name to run it.
If
gt; gt; you
gt; gt; gt; gt; are using excel 2000 or newer you may have to change the macro
security
gt; gt; gt; gt; settings to get the macro to run. To change the security settings go
to
gt; gt; gt; gt; tools, macro, security, security level and set it to medium
gt; gt; gt; gt;
gt; gt; gt; gt; you may also what to have a look here on getting started with macros
gt; gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; --
gt; gt; gt; gt; Paul B
gt; gt; gt; gt; Always backup your data before trying something new
gt; gt; gt; gt; Please post any response to the newsgroups so others can benefit
from it
gt; gt; gt; gt; Feedback on answers is always appreciated!
gt; gt; gt; gt; Using Excel 2002 amp; 2003
gt; gt; gt; gt;
gt; gt; gt; gt; quot;shawnlaceyquot; gt; wrote in message
gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; I have a weekly project report in Excell that needs to be filled
out
gt; gt; on a
gt; gt; gt; gt; gt; weekly basis till the end of the year. Each sheet is the same and
I
gt; gt; only
gt; gt; gt; gt; gt; want the user to be able to fill in the exact same cells each week
and
gt; gt; gt; gt; lock
gt; gt; gt; gt; gt; down the rest of the sheet to protect the formulas. Is there a
way to
gt; gt; all
gt; gt; gt; gt; gt; the sheets as opposed to having to lock each one individually??
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Help Please!!
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt;
gt; gt;
gt; gt;
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(quot;$A$1,$A$2,$A$3,$C$8:$I$27,$C$29:$I$38quot; amp; _
quot;,$C$40:$I$51,$C$53:$I$106,$C$108:$I$135quot; amp; _
quot;,$C$137:$I$198,$C$200:$I$215,$C$217:$I$228quot; amp; _
quot;,$C$230:$I$251,$C$253:$I$280,$C$282:$I$289quot; amp; _
quot;,$C$291:$I$294quot;).Locked = False

ws.Protect Password:=quot;1234quot;
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,

Shawnquot;Paul Bquot; wrote:

gt; Your welcome
gt;
gt; --
gt; Paul B
gt; Always backup your data before trying something new
gt; Please post any response to the newsgroups so others can benefit from it
gt; Feedback on answers is always appreciated!
gt; Using Excel 2002 amp; 2003
gt;
gt; quot;shawnlaceyquot; gt; wrote in message
gt; ...
gt; gt; Thanks, Paul.....Your the man!!!
gt; gt;
gt; gt; quot;Paul Bquot; wrote:
gt; gt;
gt; gt; gt; Shawn, I thought you had the cells unlocked and only needed to protect
gt; the
gt; gt; gt; sheets all at once, try this to unlock the cells you want and protect
gt; the
gt; gt; gt; sheets
gt; gt; gt;
gt; gt; gt; Sub Protect_All_Sheets()
gt; gt; gt; Dim ws As Worksheet
gt; gt; gt; For Each ws In ThisWorkbook.Worksheets
gt; gt; gt; 'unlock the cells below
gt; gt; gt; ws.Range(quot;$A$1,$A$2,$A$3,$C$8:$I$27,$C$29:$I$38quot; amp; _
gt; gt; gt; quot;,$C$40:$I$51,$C$53:$I$106,$C$108:$I$135quot; amp; _
gt; gt; gt; quot;,$C$137:$I$198,$C$200:$I$215,$C$217:$I$228quot; amp; _
gt; gt; gt; quot;,$C$230:$I$251,$C$253:$I$280,$C$282:$I$289quot; amp; _
gt; gt; gt; quot;,$C$291:$I$294quot;).Locked = False
gt; gt; gt;
gt; gt; gt; ws.Protect Password:=quot;123quot;
gt; gt; gt; Next ws
gt; gt; gt; End Sub
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt; Paul B
gt; gt; gt; Always backup your data before trying something new
gt; gt; gt; Please post any response to the newsgroups so others can benefit from it
gt; gt; gt; Feedback on answers is always appreciated!
gt; gt; gt; Using Excel 2002 amp; 2003
gt; gt; gt;
gt; gt; gt; quot;shawnlaceyquot; gt; wrote in message
gt; gt; gt; ...
gt; gt; gt; gt; Paul B,
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks, that allowed me to lock all sheets entirely but that also
gt; include
gt; gt; gt; gt; the cells I need to be unlocked. How do I code the macro to allow
gt; gt; gt; gt;
gt; gt; gt;
gt; (=$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
gt; gt; gt;
gt; 35,$C$137:$I$198,$C$200:$I$215,$C$217:$I$228,$C$23 0:$I$251,$C$253:$I$280,$C$
gt; gt; gt; 282:$I$289,$C$291:$I$294) to be the only fields allowed to be edited?
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks,
gt; gt; gt; gt; Shawn
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Paul Bquot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; shawnlacey, you could use a macro like this
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Sub Protect_All_Sheets()
gt; gt; gt; gt; gt; Dim ws As Worksheet
gt; gt; gt; gt; gt; For Each ws In ThisWorkbook.Worksheets
gt; gt; gt; gt; gt; ws.Protect password:=quot;123quot;
gt; gt; gt; gt; gt; Next ws
gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; and to unprotect
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Sub Unprotect_All_Sheets()
gt; gt; gt; gt; gt; Dim ws As Worksheet
gt; gt; gt; gt; gt; For Each ws In ThisWorkbook.Worksheets
gt; gt; gt; gt; gt; ws.Unprotect password:=quot;123quot;
gt; gt; gt; gt; gt; Next ws
gt; gt; gt; gt; gt; End Sub
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; And if you are new to macros, to put in this macro, from your
gt; workbook
gt; gt; gt; gt; gt; right-click the workbook's icon and pick View Code. This icon is to
gt; the
gt; gt; gt; left
gt; gt; gt; gt; gt; of the quot;Filequot; menu this will open the VBA editor, in Project
gt; Explorer
gt; gt; gt; click
gt; gt; gt; gt; gt; on your workbook name, if you don't see it press CTRL r to open
gt; the
gt; gt; gt; gt; gt; Project Explorer, then go to insert, module, and paste the code in
gt; the
gt; gt; gt; gt; gt; window that opens on the right hand side, press Alt and Q to close
gt; this
gt; gt; gt; gt; gt; window and go back to your workbook and press alt and F8, this will
gt; gt; gt; bring
gt; gt; gt; gt; gt; up a box to pick the Macro from, click on the Macro name to run it.
gt; If
gt; gt; gt; you
gt; gt; gt; gt; gt; are using excel 2000 or newer you may have to change the macro
gt; security
gt; gt; gt; gt; gt; settings to get the macro to run. To change the security settings go
gt; to
gt; gt; gt; gt; gt; tools, macro, security, security level and set it to medium
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; you may also what to have a look here on getting started with macros
gt; gt; gt; gt; gt; www.mvps.org/dmcritchie/excel/getstarted.htm
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; --
gt; gt; gt; gt; gt; Paul B
gt; gt; gt; gt; gt; Always backup your data before trying something new
gt; gt; gt; gt; gt; Please post any response to the newsgroups so others can benefit
gt; from it
gt; gt; gt; gt; gt; Feedback on answers is always appreciated!
gt; gt; gt; gt; gt; Using Excel 2002 amp; 2003
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;shawnlaceyquot; gt; wrote in message
gt; gt; gt; gt; gt; ...
gt; gt; gt; gt; gt; gt; I have a weekly project report in Excell that needs to be filled
gt; out
gt; gt; gt; on a
gt; gt; gt; gt; gt; gt; weekly basis till the end of the year. Each sheet is the same and
gt; I
gt; gt; gt; only
gt; gt; gt; gt; gt; gt; want the user to be able to fill in the exact same cells each week
gt; and
gt; gt; gt; gt; gt; lock
gt; gt; gt; gt; gt; gt; down the rest of the sheet to protect the formulas. Is there a
gt; way to
gt; gt; gt; all
gt; gt; gt; gt; gt; gt; the sheets as opposed to having to lock each one individually??
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Help Please!!
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt; gt; gt;
gt;
gt;
gt;