Allow Edit Cells in a Protected Document

[abbyfaye]

I have a macro I'm running that allows you to insert a new row and copy formulas by double clicking any cell. I've tried running the below code to allow my macro to run on a locked document, which it does...but the cells I had locked using the "Format Cells" are still editable. How am I able to lock specific cells in a range and allow my macro to run? I also need to be able to delete rows which I thought would be possible with my AllowDeletingRows:=True, but I get an error that says "The cell or chart you're trying to change is on a protected sheet. To make a change, unprotect the sheet..." Additionally, how do I add a password to protect the document?

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Offset(2).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(2).EntireRow
On Error Resume Next
Target.Offset(2).EntireRow.SpecialCells(xlConstants).ClearContents
With ActiveSheet
.EnableOutlining = True
.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True _
, AllowFormattingCells:=True, AllowDeletingRows:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowInsertingHyperlinks:=True, AllowFiltering _
:=True, userInterfaceOnly:=True
End With
End Sub

 

[Domenic]

As you know, with the userInterfaceOnly argument set to True, only the user interface is protected, not the macro. So the user can edit a cell if it's unlocked. And, since the AllowDeletingRows argument is set to True, the user can delete a row, provided that every cell in that row is unlocked. Also, since only the user interface is protect, the macro can edit cells and delete rows, whether or not they're unlocked.

Hope this helps!

 

[abbyfaye]

As you know, with the userInterfaceOnly argument set to True, only the user interface is protected, not the macro. So the user can edit a cell if it's unlocked. And, since the AllowDeletingRows argument is set to True, the user can delete a row, provided that every cell in that row is unlocked. Also, since only the user interface is protect, the macro can edit cells and delete rows, whether or not they're unlocked.

Hope this helps!

Hi Domenic,
I am in agreeance with you, this is the point I'm stuck at. I need to delete a row that contains some locked cells. If a row is added by accidentally double clicking a cell, I need to be able to have it deleted without unlocking the document. My hunch is this isn't possible, but looking for additional opinions.

 

[Domenic]

How about simply unprotect the sheet at the beginning of your code, and then protect it at the end if it, like this...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Me.Unprotect Password:="YourPassword"
    
    'Your code here
    '
    '
    
    Me.Protect Password:="YourPassword"
    
End Sub