How do I freeze individual cells with formulas in them

[Robernaut]

I work for an accounting firm and we have spreadsheets for everyday usage.
We would like to freeze only the cells in which formulas have been entered,
so that someone else going into the spreadsheet cannot accidentally delete
them. If you can help I would appreciate it.

 

[Max]

To protect only the formula cells in the sheet,
try it in this sequence ...

Select the entire sheet, click Format > Cells > Protection tab
Uncheck "Locked" > OK. This "unlocks" all the cells in the sheet

Now press F5 > Special > Check "Formulas" > OK
This selects all the formula cells on the sheet at one go
Click Format > Cells > Protection tab
Check "Locked" > OK. If you want to "hide" the formulas as well,
check "Hidden" as well in the tab before you OK out

Then just apply sheet protection
via clicking Tools > Protection > Protect Sheet > password? > OK

The above would protect only the formula cells in the sheet. And if you had
checked "Hidden" as well earlier, then the formulas cannot be seen in the
formula bar when these cells are selected.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:25,000 Files:300 Subscribers:70
xdemechanik

 

[Eduardo]

Hi,
you have to protect the worksheet so all the cells locked will not be
available to the users
To unlock the cell you want the users to have access, Highlight them right
click on the mouse, format cells, last tab is protection uncheck Locked, then
protect your sheet

if this helps please click yes, thanks

 

[Susan]

you could use data validation, under "allow" choose "custom", and
under "data" enter =""
then you can enter a message under the "error alert" tab, something to
the effect of "FORMULA CELL - NO MANUAL ENTRY ALLOWED".

i use this frequently, so frequently in fact that i recorded a macro
to the effect & put it on a button on my toolbar. it's called
"LockCellValidation" & the code behind it (in my personal.xls) is as
follows:

Sub LockFormulaValidation()

With Selection.Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="="""""
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "FORMULA CELL"
.InputMessage = ""
.ErrorMessage = "No manual calculation allowed!"
.ShowInput = False
.ShowError = True
End With

End Sub

i use excel 2000 so i don't know if this code would work for excel
2007, or where you find data validation in excel 2007.
hope that helps

susan