Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Disable Cut or Copy or Paste in Excel

This is a discussion on Disable Cut or Copy or Paste in Excel within the Excel Questions forums, part of the Question Forums category; I want to disable cut or copy or paste in a worksheet as soon as the workbook is opened Can ...

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    63

    Default Disable Cut or Copy or Paste in Excel

    I want to disable cut or copy or paste in a worksheet as soon as the workbook is opened

    Can someone help

  2. #2
    MrExcel MVP Zack Barresse's Avatar
    Join Date
    Dec 2003
    Location
    Oregon, USA
    Posts
    10,494

    Default

    Ivan F Moala has this on his website. check it out at http://xcelfiles.homestead.com/

    hth

    edit: link
    Regards,
    Zack Barresse
    My book on Excel Tables
    ExcelTables.com
    All Excel Functions
    (If you would like comments in any code, please say so.)

  3. #3
    DRJ
    DRJ is offline
    MrExcel MVP DRJ's Avatar
    Join Date
    Feb 2002
    Location
    California
    Posts
    3,856

    Default

    put

    Code:
    Application.CutCopyMode=False
    In the workbook activate and selection change events

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,011

    Default Re: Disable Cut or Copy or Paste in Excel

    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

  5. #5
    New Member
    Join Date
    Jan 2009
    Posts
    16

    Default Re: Disable Cut or Copy or Paste in Excel

    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,

  6. #6
    New Member
    Join Date
    Jan 2009
    Location
    Liverpool, UK
    Posts
    23

    Default Re: Disable Cut or Copy or Paste in Excel

    Quote Originally Posted by Tom Urtis View Post
    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.

  7. #7
    Board Regular
    Join Date
    Oct 2009
    Location
    West Texas
    Posts
    150

    Default Re: Disable Cut or Copy or Paste in Excel

    Quote Originally Posted by Tom Urtis View Post
    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?

  8. #8
    Board Regular
    Join Date
    Oct 2009
    Location
    West Texas
    Posts
    150

    Default Re: Disable Cut or Copy or Paste in Excel

    Quote Originally Posted by lol.xls View Post
    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?

  9. #9
    New Member
    Join Date
    Mar 2010
    Posts
    16

    Default Re: Disable Cut or Copy or Paste in Excel

    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

  10. #10
    New Member
    Join Date
    Sep 2011
    Posts
    1

    Default Re: Disable Cut or Copy or Paste in Excel

    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!

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com