Lock formula cells?

E

EllenM

Hello,
I'm designing a spreadsheet for my office. Is there a way to prevent my
folks from entering in data into cells with formulas?

Thanks in advance,
Ellen
 
P

Peo Sjoblom

You can select all cells in the worksheet, then do format>cells>protections
and check unlocked, then with all cells still selected press F5, then
special and select formulas, click OK and do format>cells>protection and
select locked, finally protect the sheet under tools>protection. It won't
protect it from the most computer savvy that can Google for password
breakers
 
E

EllenM

Hi Gary,
Thanks for your help.

I'd like for only the cells with formulas to be locked. When did as you
outlined the entire sheet was locked. Did I miss something?

Thanks,
Ellen
 
G

Gary''s Student

Hi Ellen

After step 3, the only cells selected should be formula cells
Step 4 should then lock only those formula cells
Step 5 should activate the locks
 
G

Gary''s Student

1. unprotect the sheet
2. unlock all cells
3. Edit > Goto > Special > Formulae
4. Format > Cells... > Protection > Locked
5. protect the sheet

To automate this, use this macro:

Sub Macro2()
ActiveSheet.Unprotect
Cells.Select
Selection.Locked = False
Selection.FormulaHidden = False
Selection.SpecialCells(xlCellTypeFormulas, 23).Select
Selection.Locked = True
ActiveSheet.Protect Contents:=True
ActiveSheet.EnableSelection = xlSelection

End Sub
 
E

EllenM

Hi Gary's Student,
Your script works beautifully. Looks like I need some work to protect
formulas cells by hand.

Thanks so much!!

Ellen
 
Top