-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Disable Cut or Copy or Paste in Excel
- Thread starter sree_31
- Start date
Zack Barresse
MrExcel MVP
- Joined
- Dec 9, 2003
- Messages
- 10,881
- Office Version
- 365
- 2010
- Platform
- Windows
- Mobile
- Web
Ivan F Moala has this on his website. check it out at http://xcelfiles.homestead.com/
hth
edit: link
hth
edit: link
Upvote
0
Tom Urtis
MrExcel MVP
- Joined
- Feb 10, 2002
- Messages
- 11,305
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
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,
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
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
incorperate an exception to a macro in this code?
Upvote
0
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
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!
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
Similar threads
- Solved
