Excel: Allow user to delete a row containing protected cells – crashes

Question

I ve added this VBA do my document. But the code keeps crashing at this line:

ActiveSheet.Protect

I have no idea where to start?1

Excel: allow user to delete a row containing protected cells

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address Like "$" & Target.Row & ":$" & Target.Row Then
ActiveSheet.Unprotect
Else
ActiveSheet.Protect
End If
End Sub

Update: The error occurs whenever I select a protected cell twice in a row.

try this ActiveSheet.Protect contents:=True, userinterfaceonly:=True — pokemon_Man
You can't protect an already protected sheet. You could try adding ActiveSheet.Unprotect before ActiveSheet.Protect. — Lisa
@Lisa11: Good idea. But it does not help. I now got an error on the new Line 'ActiveSheet.Unprotect' — fabian

R. Roe R. Roe · Accepted Answer · 2018-01-04T15:06:23

I didn't have any issues with your code snippet, but you could only protect the sheet when it is not protected like so:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address Like "$" & Target.Row & ":$" & Target.Row Then
        ActiveSheet.Unprotect
    Else
        If Not ActiveSheet.ProtectContents Then
            ActiveSheet.Protect
        End If
    End If
End Sub

This just wraps the protect statement in an if statement that checks to see if the sheet is not protected before it tries to protect the sheet.

Excel: Allow user to delete a row containing protected cells – crashes

1 Answers