Lock formula cells?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
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
 
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
 
Hi Gary's Student,
Your script works beautifully. Looks like I need some work to protect
formulas cells by hand.

Thanks so much!!

Ellen
 
Back
Top