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
Answer accepted by question author
Anonymous
2011-10-09T15:11:54+00:00

Realizing that you may not already have any macros in the workbook and may not want to add any, but I will mention that if you do already have them, then you could add one that would allow the user to select a cell on a row and then run the macro (through a button?) which would then unprotect the worksheet, insert a row, and reprotect the sheet.

If your sheet is protected without a password, the code might look similar to this:

Sub InsertNewRow()

  ActiveSheet.Unprotect

  Selection.EntireRow.Insert

  ActiveSheet.Protect

End Sub

If your sheet is protected with a password, the code would look similar to this, but provide the actual password:

Sub InsertNewRow()

    ActiveSheet.Unprotect password:="sheet password"

    Selection.EntireRow.Insert

    ActiveSheet.Protect password:="sheet password"

End Sub

Was this answer helpful?

0 comments

7 additional answers

Sort by: Most helpful
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
    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
  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. 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