Protect Cells

Drdave1958 · Mar 23rd, 2002, 11:19 AM

Is there a way to protect only certain cells on a sheet from being changed (e.g. formulas)?

Thanks, Dave

Yogi Anand · Mar 23rd, 2002, 11:32 AM

Quote:

On 2002-03-23 10:19, Drdave1958 wrote:
Is there a way to protect only certain cells on a sheet from being changed (e.g. formulas)?

Thanks, Dave

Hi Drdave1958:
Select the cell you want to protect -- then FORMAT|CELLS|PROTECTION|Locked
Now the Locking of the cells will take effect only after you protect the worksheet. If you do not protect the worksheet, locking of the cells has no effect whatsoever.
HTH

Drdave1958 · Mar 23rd, 2002, 11:39 AM

Thanks, Yogi but what I'm after is to allow the end user to input data into cells other than the ones that contain my formulas. Am I missing something here?

Mudface · Mar 23rd, 2002, 11:43 AM

Quote:

On 2002-03-23 10:39, Drdave1958 wrote:
Thanks, Yogi but what I'm after is to allow the end user to input data into cells other than the ones that contain my formulas. Am I missing something here?

Select your entire sheet (or the cells you anticipate will be used), click on Format-Cells-Protection and uncheck the 'locked' box. Then select the cells you don't want to be changed and click on Format-Cells-Protection and recheck the 'locked' box. Then click on Tools- Protection- Protect Sheet and enter a password if you want. HTH.

Drdave1958 · Mar 23rd, 2002, 11:49 AM

Thats the ticket!
Thanks Mudface

Harryp · Apr 11th, 2002, 07:19 AM

This helped me as well.

But is their a way of selecting all the cells containing formula's in a sheet so that you can protect them in one go ?.

Hope you can help.

shades · Apr 11th, 2002, 07:31 AM

Obviously, hold down the CONTROL key as you select each one. Then follow the procedure above. That may be tedious if you have many cells. But I have done it with as many as 100 cells. Works fine.

Harryp · Apr 11th, 2002, 07:49 AM

Thanks Shades
I have a lot more than 100cells.
I have currently done it by selecting the full column or row of data that contains the formula's.
I am looking for a hidden feature (well hidden to me anyway) that must exist such as 'Select all' then you can choose Formulas.
If it doesn't currently exist perhaps Bill will oblige!

Chris Davison · Apr 12th, 2002, 12:48 PM

Hi Harry,

try this :

. select the whole sheet (top right button to highlight everything)
. right click within the highlighted range
. format cells
. protection
. unlock them all (uncheck the "locked" box)
. click "ok"

you've just unprotected every cell in the sheet

.select the whole sheet (top right button to highlight everything)
. edit (from menu bar)
. goto
. special
. formulas (formulae)
. numbers / text / logicals / error should all be checked
. hit "ok"

you have now selected all the formulae in your sheet

. right click inside one of those highlighted cells
. format cells
. protection
. lock them all (check the "locked" box)
. click "ok"

you've just protected every cell in the sheet
that was highlighted, with the only highlighted cells being those which contain formulae

ergo, your formulae are protected

but we need to protect the sheet in order for this to come into force :

. tools
. protection
. protect sheet
. add a password if you're in Dirty Harry mode today
. click "ok"

try adding numbers to blank cells, should be okay.. now try amending a cell you know to have a formula in it and you should get a "freeze...LAPD" warning

Harryp · Apr 15th, 2002, 10:39 AM

Chris
Just what I needed. Perfect !
No need to ask Bill now.
Cheers

Apr 11th, 2002, 07:31 AM	#7
shades Board Regular Join Date: Mar 2002 Location: Near the Land of Oz Posts: 1,550	Obviously, hold down the CONTROL key as you select each one. Then follow the procedure above. That may be tedious if you have many cells. But I have done it with as many as 100 cells. Works fine. __________________ - old, slow, and confused ... but at least I'm inconsistent - (retired Excel 2003 user, 3.28.2008)

Apr 12th, 2002, 12:48 PM	#9
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Hi Harry, try this : . select the whole sheet (top right button to highlight everything) . right click within the highlighted range . format cells . protection . unlock them all (uncheck the "locked" box) . click "ok" you've just unprotected every cell in the sheet .select the whole sheet (top right button to highlight everything) . edit (from menu bar) . goto . special . formulas (formulae) . numbers / text / logicals / error should all be checked . hit "ok" you have now selected all the formulae in your sheet . right click inside one of those highlighted cells . format cells . protection . lock them all (check the "locked" box) . click "ok" you've just protected every cell in the sheet that was highlighted, with the only highlighted cells being those which contain formulae ergo, your formulae are protected but we need to protect the sheet in order for this to come into force : . tools . protection . protect sheet . add a password if you're in Dirty Harry mode today . click "ok" try adding numbers to blank cells, should be okay.. now try amending a cell you know to have a formula in it and you should get a "freeze...LAPD" warning __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 23rd, 2002, 11:19 AM	#1
Drdave1958 Board Regular Join Date: Mar 2002 Location: Sitting here inside myself Posts: 204	Is there a way to protect only certain cells on a sheet from being changed (e.g. formulas)? Thanks, Dave

Mar 23rd, 2002, 11:39 AM	#3
Drdave1958 Board Regular Join Date: Mar 2002 Location: Sitting here inside myself Posts: 204	Thanks, Yogi but what I'm after is to allow the end user to input data into cells other than the ones that contain my formulas. Am I missing something here?

Mar 23rd, 2002, 11:49 AM	#5
Drdave1958 Board Regular Join Date: Mar 2002 Location: Sitting here inside myself Posts: 204	Thats the ticket! Thanks Mudface

Apr 11th, 2002, 07:19 AM	#6
Harryp New Member Join Date: Apr 2002 Posts: 6	This helped me as well. But is their a way of selecting all the cells containing formula's in a sheet so that you can protect them in one go ?. Hope you can help.

Apr 11th, 2002, 07:49 AM	#8
Harryp New Member Join Date: Apr 2002 Posts: 6	Thanks Shades I have a lot more than 100cells. I have currently done it by selecting the full column or row of data that contains the formula's. I am looking for a hidden feature (well hidden to me anyway) that must exist such as 'Select all' then you can choose Formulas. If it doesn't currently exist perhaps Bill will oblige!

Apr 15th, 2002, 10:39 AM	#10
Harryp New Member Join Date: Apr 2002 Posts: 6	Chris Just what I needed. Perfect ! No need to ask Bill now. Cheers