Is it possible to lock a formula on a template without protecting the sheet?

Anonymous
2011-10-06T15:07:52+00:00

I'm trying to lock the formula cells so that they can't be tampered with, but if I protect the sheet it prevents rows from being copied and pasted (even with the "insert rows" option ticked) without unprotecting the sheet, and when the sheet is unprotected the formulas can also be changed, which defeats the object.  I need to have the option of inserting rows but I also want to make sure that the formulas aren't altered.

Is there a way of achieving both objectives?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Oldest
Most helpful Newest Oldest
  1. Anonymous
    2011-10-06T15:45:48+00:00

    Hi, do this, first highlight the whole spreadsheet, right click on the mouse, format cells, protection, uncheck locked, then do the same for the cell with the formula and locked it. Now protect the sheet, you will be able to perform all what you need but change the formula in that cell

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2011-10-07T08:30:57+00:00

    Sorry ediarp, seems you didn't read the question thoroughly.  What I want to do is NOT protect the worksheet, because I need to retain the ability to add rows (via copy & insert), and I can only do this if I unprotect the sheet, which then defeats the object as this allows the locked cells to be edited.  So although your answer was textbook to the help menu, it doesn't help my particular case.

    Any other thoughts anyone?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2011-10-09T14:36:46+00:00

    You will not be able to lock the formula unless you protect the whole sheet. however you can create a vba code(macro)

    Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

    If ActiveCell.HasFormula Then

    MsgBox ("Formula in cell do not remove or change!")

    Else

    End If

    End Sub

    Was this answer helpful?

    0 comments
  4. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  5. Anonymous
    2013-10-02T01:29:56+00:00

    JLLatham answered quite well ... above the replies... will see if it works but the macro sounds great

    Regards

    Was this answer helpful?

    0 comments