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 formatcellsprotections
and check unlocked, then with all cells still selected press F5, then
special and select formulas, click OK and do formatcellsprotection and
select locked, finally protect the sheet under toolsprotection. It won't
protect it from the most computer savvy that can Google for password
breakers


--
Regards,

Peo Sjoblom



"EllenM" wrote in message
...
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

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200731


"EllenM" wrote:

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

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
--
Gary''s Student - gsnu200731


"EllenM" wrote:

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

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200731


"EllenM" wrote:

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

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

--
Gary''s Student - gsnu200731


"EllenM" wrote:

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

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

Thanks so much!!

Ellen

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200731


"EllenM" wrote:

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