Excel: allow user to delete a row containing protected cells

Question

I need to preserve some cells, containing formulas, against the user to accidantally modify them. To do so, i protected the cell and locked the worksheet. Doing so, will disallow the user to delete those rows.

How can i do to detect if the user is deleting the WHOLE row, and allow this action not dependingly on if the row contains protected cells or not?

I thought to trigger the "right click -> delete row" action with VBA, but i can't find any documentation about this...

Also, I don't want to use dedicated masks or buttons to don't complicate the UI...

Answer 1

You can try this simple code:

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

Answer 2

Afaik there isn't a way to trap a"delete row" event. You can respond to Worksheet.BeforeRightClick, though this doesn't help you much as you don't know at that point what a user is going to do.

One approach to your problem could be to create a custom menu item on the right-click menus which will delete the protected row. You can do this in the Workbook_Active macro so it's there every time the user opens the workbook. You should also remove it when the workbook is deactivated (otherwise it will appear on unrelated spreadsheets the user has open). For example - add the following code to ThisWorkbook:

Private Sub Workbook_Activate()
        modManageMenus.addMenuItems
End Sub

Private Sub Workbook_Deactivate()
        modManageMenus.removeMenuItems
End Sub

And then add a new module modManageMenus with the following:

Public Sub addMenuItems()
        With Application.CommandBars("cell").Controls.Add(msoControlButton, , , 1, True)
                .Caption = "Delete protected row"
                .OnAction = "modManageMenus.deleteRow"
        End With
        With Application.CommandBars("row").Controls.Add(msoControlButton, , , 1, True)
                .Caption = "Delete protected row"
                .OnAction = "modManageMenus.deleteRow"
        End With
End Sub


Public Sub removeMenuItems()
        Dim ctrl As CommandBarControl
        For Each ctrl In Application.CommandBars("cell").Controls
                If ctrl.Caption = "Delete protected row" Then ctrl.Delete
        Next
        For Each ctrl In Application.CommandBars("row").Controls
                If ctrl.Caption = "Delete protected row" Then ctrl.Delete
        Next
End Sub

Public Sub deleteRow()
        ActiveSheet.Unprotect
        Selection.EntireRow.Delete
        ActiveSheet.Protect
End Sub

You may need to modify the code within deleteRow (e.g. by adding a password, limiting which rows the user can/can't delete etc) but this should give you an idea.