Dim ChkFormula As Boolean, PrevFormula As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'if sheet is not protected
If ActiveSheet.ProtectionMode = False Then
'check if selected cell contains formula
ChkFormula = Target.HasFormula
'if selected cell contains formula, save formula to variable
If ChkFormula = True Then PrevFormula = Target.Formula
End If
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'if sheet is not protected
If ActiveSheet.ProtectionMode = False Then
'if changed cell contains formula
If ChkFormula = True Then
Application.EnableEvents = False
'display message box
MsgBox "This cell contains a formula. " _
& vbCrLf & "Changing this cell is not allowed.", _
vbExclamation, "Formula Found"
'replace formula in changed cell
Target.Formula = PrevFormula
Application.EnableEvents = True
End If
End If
End Sub