Protect Cells

Drdave1958

Board Regular
Joined
Mar 10, 2002
Messages
204
Is there a way to protect only certain cells on a sheet from being changed (e.g. formulas)?

Thanks, Dave
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top