Protect formula cells

[CAM]

Hello,

I am using Excel 2003 and I am wondering if there is a easy way in
protecting formula cells in my workbook. I have about 25 formula cells and
it is all over the workbook. My users keep changes the formula cells, they
suppose to enter data not changing formulas. I have about 20 workbooks to
do. Is there an easy way or global way to protect formulas using a password
on a sheet? I don't want to go cell by cell. Can anybody help me on this
one? Your help will be appreciated, thank you in advance.

 

[Roger Govier]

Hi Alex

The default property for all cells on a sheet is Locked.
You would need to
select the cells on a sheet where you want users to be able to enter
data
Format>Cells>Protection>uncheck Locked
Tools>Protection>Protect sheet.

Now the only cells where users can enter anything, is in the cells you
have specifically unlocked.

You might want to do it the other way around, by selecting all cells in
the sheet by pressing the small gray cell above row 1 and left of column
A. This will select the whole sheet. Format all cells as unlocked.
Select your cells with formulae, set property to locked, then protect
sheet.

 

[CAM]

Thanks

Hi Alex

The default property for all cells on a sheet is Locked.
You would need to
select the cells on a sheet where you want users to be able to enter data
Format>Cells>Protection>uncheck Locked
Tools>Protection>Protect sheet.

Now the only cells where users can enter anything, is in the cells you
have specifically unlocked.

You might want to do it the other way around, by selecting all cells in
the sheet by pressing the small gray cell above row 1 and left of column
A. This will select the whole sheet. Format all cells as unlocked.
Select your cells with formulae, set property to locked, then protect
sheet.

 

[Gord Dibben]

CAM

If you want to speed Roger's process up a bit..................

You could use a macro that would lock just the cells with formulas on each sheet
in a workbook.

Sub protect_formulas()
Dim wks As Worksheet
Dim wkbk As Workbook
Dim cl As Range
Set wkbk = ActiveWorkbook
For Each wks In wkbk.Worksheets
wks.Cells.Locked = False
For Each cl In wks.UsedRange
If cl.HasFormula Then
cl.Locked = True
End If
Next cl
wks.Protect
Next wks
End Sub


Gord Dibben MS Excel MVP