Protect a formula

[Guest]

Hi all,
I have a protected sheet that only a specific user can open with password
and edit it. One of the cells has a formula and i want to restrict the user
from deleting the specified cell. Any suggestions?
Thanks in advance

 

[Harald Staff]

If you are allowing the user to unlock the sheet, then I'd create a macro,
called from the worksheet_change event, that reinstates the formula if it
gets altered or removed.

HTH. Best wishes Harald

 

[Guest]

Hi Harald,
Thanks a lot for replying. I've tried to wriet a code to do that but got
messy. Might you have a sample code so i could practice?
Thanks a lot.

 

[Harald Staff]

Sure. Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
If Intersect(Target, Me.Range("B1")) Is Nothing Then Exit Sub
If Me.Range("B1").FormulaR1C1 <> _
"=IF(R2C1<>0,R1C1/R2C1,0)" Then
Application.EnableEvents = False
Me.Range("B1").FormulaR1C1 = _
"=IF(R2C1<>0,R1C1/R2C1,0)"
Application.EnableEvents = True
End If
End Sub

It secures the formula in B1. This goes in the worksheet module (rightclick
sheet tab, "view code" to access). Note its behavior when you insert/delete
rows and columns.

HTH. best wishes Harald