Disable Cut or Copy or Paste in Excel

sree_31

Board Regular
Joined
Apr 18, 2002
Messages
63
I want to disable cut or copy or paste in a worksheet as soon as the workbook is opened

Can someone help
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

DRJ

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
put

Code:
Application.CutCopyMode=False

In the workbook activate and selection change events
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,206
In addition to Ivan's fine code with command bars, here is an alternative approach that can be placed in the workbook module to cover cut, copy, paste, and drag & drop:


Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click menu deactivated." & vbCrLf & _
"Cannot copy or ''drag & drop''.", 16, "For this workbook:"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub
 

Jibse

New Member
Joined
Jan 8, 2009
Messages
38

ADVERTISEMENT

Hello,

I would like to know which is the best code, the one of DRJ (very simple, but which trigger event selection change) or the one of Tom Urtis ?
Often, I am wondering if the fact of trigger event very often and unnecessarily (this is the cas for selection.change) is or not an inconvenient.
Thanks,
 

FATS

New Member
Joined
Jan 16, 2009
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click menu deactivated." & vbCrLf & _
"Cannot copy or ''drag & drop''.", 16, "For this workbook:"
End Sub

The above works fine for me, but how can I disable the right clicking on all of the imported pictures in my entire workbook?

Thanks in advance.
 

lol.xls

Board Regular
Joined
Oct 5, 2009
Messages
174

ADVERTISEMENT

In addition to Ivan's fine code with command bars, here is an alternative approach that can be placed in the workbook module to cover cut, copy, paste, and drag & drop:


Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click menu deactivated." & vbCrLf & _
"Cannot copy or ''drag & drop''.", 16, "For this workbook:"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub

I currently have a macro to copy certain data in a workbook and transfer it to the new sheet conditionally. can i use the code above but allow my existing macro to run?
 

lol.xls

Board Regular
Joined
Oct 5, 2009
Messages
174
I currently have a macro to copy certain data in a workbook and transfer it to the new sheet conditionally. can i use the code above but allow my existing macro to run?

incorperate an exception to a macro in this code?
 

juliagaskell

New Member
Joined
Mar 2, 2010
Messages
16
Hi there,
I have found your code very useful and have most of it in the This workbook section and it works well. I also want a macro to re-enable the right click and cut, for when users submit the file back to me and I need to manipulate it. I have managed to get the cut to work, using the following, but cannot figure out how to get the right click to work.

Sub AllowCut()
' enables cut using ctrl x and cell dragging and right click
Application.CellDragAndDrop = True
Application.OnKey "^x"
Application.CutCopyMode = True
Application.CommandBars("Cell").Reset This should re-enable the right click but it doesn't
End Sub

The right click is disabled as follows in the This workbook module and works fine:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click menu deactivated." & vbCrLf & _
"Cannot Cut or ''drag & drop''. Use ctrl c and ctrl v to copy and paste cells. " & vbCrLf & _
"To delete rows, select the row(s) and use ctrl -", 16, "This workbook:"
End Sub


Thanks for any ideas
 

joeipp98

New Member
Joined
Sep 27, 2011
Messages
1
Hi Everyone,

I am having the same issues. After copying and pasting that code into my excel spreadsheet, I have not found an effective way to RE-enable copy and paste for the purposes of manipulating the data from the many people working on their own spreadsheets and submitting them to me for a master sheet to be compiled.

Appreciate any help!
 

Watch MrExcel Video

Forum statistics

Threads
1,108,751
Messages
5,524,621
Members
409,595
Latest member
therevford

This Week's Hot Topics

Top