Worksheet.Protect AllowDeletingRows does not allow deleting rows

Question

In my workbook I'm protecting all sheets on Workbook_Open, with the following code:

ws.Protect Password:="password", UserInterFaceOnly:=True, _
AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingColumns:=True, AllowInsertingRows:=True, _
AllowDeletingColumns:=True, AllowDeletingRows:=True

In the last line I specifically turn on AllowDeletingRows to allow the user to delete rows. Inserting rows works just fine, but if I try to delete a row on the protected sheet Excel tells me I can't delete rows that contain locked cells. Does anyone know how to get AllowDeletingRows working properly?

Louis Louis · Accepted Answer · 2019-09-17T09:53:28

Reading from Microsoft Documentation:

[...] The rows containing the cells to be deleted must be unlocked when the sheet is protected.

This means that when the Sheet is protected, you have to unlock the Rows first if you want to delete them.

Example

In this example you can delete the first Row (following the first line) or the entire Sheet (following the second line) by unlocking it.

Sub ProtectionOptions() 
   'Unlock only row 1. 
   Rows("1:1").Locked = False

   'Or Unlock all cells. 
   Cells.Locked = False  

End Sub

Edit

This Sub allows you to ask the user for which Row to delete and you can set AllowDeletingRows:=False, since you'll not be using the Excel Delete function. Here's the code:

Sub Setup()
    ws.Protect Password:="password", UserInterFaceOnly:=True, _
    AllowFormattingColumns:=True, AllowFormattingRows:=True, _
    AllowInsertingColumns:=True, AllowInsertingRows:=True, _
    AllowDeletingColumns:=True, AllowDeletingRows:=False
End Sub

'Connect this Sub to a button.
Sub DeleteRow()
    Dim userInput As String
    Dim row As Long

    userInput = InputBox("Please enter the row number you want to delete:", "Delete Row")
    If Not IsNumeric(userInput) Then
        MsgBox "Provided input is not numeric. Please insert a valid row number", vbExclamation
        Exit Sub
    End If

    row = userInput

    ws.rows(row).Locked = False
    ws.rows(row).Delete

End Sub

Note: This code is tested and it works.

Hope this helps.

Worksheet.Protect AllowDeletingRows does not allow deleting rows

1 Answers

Example

Edit