Disable Delete

[BluesBros]

I've a worksheet which I want people to update. The only thing is I do not want them to be able to delete any rows or celss from it I've tried doing a search but cannot find what I'm after.

 

[BrianB]

The usual way is to protect everything and use userforms to change the contents.

Something like this needs to be the first line of a macro. It allows macro code to make changes but not users. The sheet reverts to normal protection when the workbook is closed.

Worksheets("Sheet1").Protect password:="xxx", userinterfaceonly:=True

 

[btadams]

here's an example of disabling commands. Copy & paste the following code into a standard code module:

Option Explicit
 
Sub ToggleCutCopyAndPaste(Allow As Boolean)
     'Activate/deactivate cut, copy, paste and pastespecial menu items
'    Call EnableMenuItem(21, Allow) ' cut
'    Call EnableMenuItem(19, Allow) ' copy
'    Call EnableMenuItem(22, Allow) ' paste
'    Call EnableMenuItem(755, Allow) ' pastespecial
    Call EnableMenuItem(478, Allow) 'Delete on Edit menu
    Call EnableMenuItem(847, Allow) 'Delete Sheet on Edit menu
    Call EnableMenuItem(848, Allow) 'Move or Copy Sheet on Edit menu
    Call EnableMenuItem(292, Allow) 'Delete on Cell menu
    Call EnableMenuItem(294, Allow) 'Delete on Column menu
    Call EnableMenuItem(293, Allow) 'Delete on Row menu

     
     'Activate/deactivate drag and drop ability
'    Application.CellDragAndDrop = Allow
     
     'Activate/deactivate cut, copy, paste and pastespecial shortcut keys
    With Application
        Select Case Allow
        Case Is = False
'            .OnKey "^c", "CutCopyPasteDisabled"
'            .OnKey "^v", "CutCopyPasteDisabled"
'            .OnKey "^x", "CutCopyPasteDisabled"
'            .OnKey "+{DEL}", "CutCopyPasteDisabled"
'            .OnKey "^{INSERT}", "CutCopyPasteDisabled"
        Case Is = True
'            .OnKey "^c"
'            .OnKey "^v"
'            .OnKey "^x"
'            .OnKey "+{DEL}"
'            .OnKey "^{INSERT}"
        End Select
    End With
End Sub
 
Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean)
     'Activate/Deactivate specific menu item
    Dim cBar As CommandBar
    Dim cBarCtrl As CommandBarControl
    For Each cBar In Application.CommandBars
        Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
        If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
    Next
End Sub
 
Sub CutCopyPasteDisabled()
     'Check to see if
    MsgBox "Sorry!  Cutting, copying and pasting have been disabled in this workbook!"
End Sub

Then copy/paste the code below into the ThisWorkBook module:

Option Explicit
 
Private Sub Workbook_Activate()
    Call ToggleCutCopyAndPaste(False)
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call ToggleCutCopyAndPaste(True)
End Sub
 
Private Sub Workbook_Deactivate()
    Call ToggleCutCopyAndPaste(True)
End Sub
 
Private Sub Workbook_Open()
    Call ToggleCutCopyAndPaste(False)
End Sub

 

[Oaktree]

Would just protecting the sheet (tools --> protection --> protect sheet) get you what you want?

(assuming you have locked/unlocked the appropriate cells)