Check if user selects entire row, right-click and selects delete

[piyo27]

I have a protected worksheet with locked cells. Will need to unprotect and worksheet when perform delete entirerow action. Is there any way to check if user selects entire row, right-click and selects delete?

 

[sijpie]

I don't think so. But you don't need to make it complicated: when you lock the worksheet you can specify that the user is allowed to remove a row. Look in the table that comes up when protecting the sheet. It shows this option. Put a tick in front of it.

 

[piyo27]

My cell is locked and the worksheet is protected. I did indicate user is allowed to remove a row. When I tried to remove a row, I'm prompted to unprotect the worksheet.

I don't think so. But you don't need to make it complicated: when you lock the worksheet you can specify that the user is allowed to remove a row. Look in the table that comes up when protecting the sheet. It shows this option. Put a tick in front of it.

 

[sijpie]

Yes you are correct. The row can then only be deleted if there are no locked cells in the row. Not very useful.
I would suggest that you put a button on the worksheet, call it 'Delete Row' and link it to the DelRow macro below.

Copy these two subs into an empty macro module:

Option Explicit

Const mcsPW As String = "" '<<<< Enter the worksheet pasword here <<<<

Sub DelRow()
' /////////////////////////////////////////////
' // Sub to check if row can be deleted. _
  // If so pass on row to the delete function _
  /////////////////////////////////////////////
    Dim mbR As VbMsgBoxResult
    Dim lRw As Long
    
    lRw = ActiveCell.Row
    Select Case lRw
        Case 1, 2, 3, 8  '<<< Enter any row numbers here that arte not allowed to be deleted, like headers and the row(s) containing the macro button
            MsgBox "These rows cannot be deleted"
        Case Else
            mbR = MsgBox("Are you sure you want to delete row " & lRw & "?", Buttons:=vbYesNo)
            If mbR = vbYes Then
                DoRowDel lRw
            End If
    End Select
End Sub

Private Sub DoRowDel(lR As Long)
' /////////////////////////////////////////////
' // Sub to delete the row passed as argument _
  // after unprotecting sheet. Then protect again _
  /////////////////////////////////////////////
    With ActiveSheet
        .Unprotect mcsPW
        .Rows(lR).EntireRow.Delete
        .Protect Password:=mcsPW
    End With
End Sub

 

[piyo27]

Thanks for the advice and solution

Yes you are correct. The row can then only be deleted if there are no locked cells in the row. Not very useful.
I would suggest that you put a button on the worksheet, call it 'Delete Row' and link it to the DelRow macro below.

Copy these two subs into an empty macro module:

Option Explicit

Const mcsPW As String = "" '<<<< Enter the worksheet pasword here <<<<

Sub DelRow()
' /////////////////////////////////////////////
' // Sub to check if row can be deleted. _
  // If so pass on row to the delete function _
  /////////////////////////////////////////////
    Dim mbR As VbMsgBoxResult
    Dim lRw As Long
    
    lRw = ActiveCell.Row
    Select Case lRw
        Case 1, 2, 3, 8  '<<< Enter any row numbers here that arte not allowed to be deleted, like headers and the row(s) containing the macro button
            MsgBox "These rows cannot be deleted"
        Case Else
            mbR = MsgBox("Are you sure you want to delete row " & lRw & "?", Buttons:=vbYesNo)
            If mbR = vbYes Then
                DoRowDel lRw
            End If
    End Select
End Sub

Private Sub DoRowDel(lR As Long)
' /////////////////////////////////////////////
' // Sub to delete the row passed as argument _
  // after unprotecting sheet. Then protect again _
  /////////////////////////////////////////////
    With ActiveSheet
        .Unprotect mcsPW
        .Rows(lR).EntireRow.Delete
        .Protect Password:=mcsPW
    End With
End Sub