Protecting formulas

[Ed]

How can i protect a formula from either being edited or
deleted?

thx

 

[Norman Harker]

Hi Ed!

If it's a single formula:

Select all cells on the sheet by clicking the button at the
intersection of Row and Column heads
Format > Cells > Protection
Remove the check from "Locked"
OK
Select the cell(s) to be protected
Format > Cells > Protection
Check "Locked"
OK
Tools > Protection > Protect Sheet
Give and confirm a password (optional)
OK

If there's a possibility of sheet deletion to be taken into account,
you must also use:

Tools > Protection > Protect Workbook
Give and confirm a password (optional)
OK

But be aware that internal Excel protection passwords are easily
circumvented and the best way to regard this is protection against
accidental changes or deletion.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[David]

Ed wrote

How can i protect a formula from either being edited or
deleted?

thx

Here's what I put in the sheet module for the formulas I wanted to protect:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NoEdit As Range
Set NoEdit = Intersect(Target, Range("E39:E41")) '<-- change to suit
If Not NoEdit Is Nothing Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Sorry, You are not allowed to change this cell!", vbCritical,
"Permission Denied!"
End If
End Sub