Protect / Unprotect causing unwanted changes to "protection properties"

[GeeJayWhite]

Hello - this is my first post so I hope I am doing it right!

I have some code to insert a row in a worksheet based on double-clicking - the inserted row auto-fills a cell with a formula from the double-clicked row.

The formula cell is protected to ensure it can't be edited so I have included an "unprotect" command first and a "protect" command at the end to re-protect the sheet.

The issue is that the protection properties change after running the code - initially the protection allows the user to insert or delete a row but after running the code row insertion/deletion is not allowed.

Here is the code:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' Unprotect worksheet in order to autofill protected cell with formula
ActiveSheet.Unprotect "password"
' code to insert row below cell which is double-clicked and autofilling a (protected) cell with a formula
Cancel = True
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
'Turn protection back on
ActiveSheet.Protect "password"
End Sub


Does anyone have any ideas how to continue to allow row insertion/deletion after running this code?

Thanks!

[TMS]

Record a macro when you protect the sheet. It will then include the options you select. You just have to edit the recorded code to add the password.

[GeeJayWhite]

Thanks for your help - that worked very well for me!

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.