Disable Delete

BluesBros

New Member
Joined
Jan 16, 2005
Messages
42
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.
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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.
Code:
Worksheets("Sheet1").Protect password:="xxx", userinterfaceonly:=True
 

btadams

Well-known Member
Joined
Jan 6, 2003
Messages
1,936
here's an example of disabling commands. Copy & paste the following code into a standard code module:

Code:
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:

Code:
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,988
Would just protecting the sheet (tools --> protection --> protect sheet) get you what you want?

(assuming you have locked/unlocked the appropriate cells)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,760
Messages
5,574,079
Members
412,566
Latest member
TexasTony
Top