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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
put

Code:
Application.CutCopyMode=False

In the workbook activate and selection change events
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top