Results 1 to 8 of 8

Thread: How to disable Copy and Paste features in MS Excel

  1. #1
    Join Date
    Aug 2006
    Posts
    227

    How to disable Copy and Paste features in MS Excel

    Hi friends , is it possible to disable copy and paste commands in excel 2003 and 2007 ?
    I do to dead flowers what people at morgues do to dead people. Suck all the moisture out, dip them in plastic, paint them up pretty and put them in a nice frame.

  2. #2
    Join Date
    Apr 2008
    Posts
    438

    Re: How to disable Copy and Paste features in MS Excel

    To disable copy and paste command in 2003, you can install this code into the workbook:

    Option Explicit

    Sub EnableControl(Id As Integer, Enabled As Boolean)
    Dim CB As CommandBar
    Dim C As CommandBarControl
    For Each CB In Application.CommandBars
    Set C = CB.FindControl(Id:=Id, recursive:=True)
    If Not C Is Nothing Then C.Enabled = Enabled
    Next
    End Sub

    Private Sub Workbook_Activate()
    EnableControl 21, False ' cut
    EnableControl 19, False ' copy
    EnableControl 22, False ' paste
    EnableControl 755, False ' pastespecial
    Application.OnKey "^c", ""
    Application.OnKey "^v", ""
    Application.OnKey "+{DEL}", ""
    Application.OnKey "+{INSERT}", ""
    Application.CellDragAndDrop = False
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    EnableControl 21, True ' cut
    EnableControl 19, True ' copy
    EnableControl 22, True ' paste
    EnableControl 755, True ' pastespecial
    Application.OnKey "^c"
    Application.OnKey "^v"
    Application.OnKey "+{DEL}"
    Application.OnKey "+{INSERT}"
    Application.CellDragAndDrop = True
    End Sub

    Private Sub Workbook_Deactivate()
    EnableControl 21, True ' cut
    EnableControl 19, True ' copy
    EnableControl 22, True ' paste
    EnableControl 755, True ' pastespecial
    Application.OnKey "^c"
    Application.OnKey "^v"
    Application.OnKey "+{DEL}"
    Application.OnKey "+{INSERT}"
    Application.CellDragAndDrop = True
    End Sub

    Private Sub Workbook_Open()
    EnableControl 21, False ' cut
    EnableControl 19, False ' copy
    EnableControl 22, False ' paste
    EnableControl 755, False ' pastespecial
    Application.OnKey "^c", ""
    Application.OnKey "^v", ""
    Application.OnKey "+{DEL}", ""
    Application.OnKey "+{INSERT}", ""
    Application.CellDragAndDrop = False
    End Sub

    • Open up your workbook.
    • Get into VBA (Press Alt+F11)
    • Double click on (This WorkBook) in the left-hand pane
    • Copy and Paste in the code (given below)
    • Save your sheet.



    so now when the sheet is opened, the copy and paste functions will be disabled. When you close the sheet, they will be re-enabled. but Unfortunately, if the user selects "disable macros" when opening the sheet, they won't work...the sheet will open with cut/copy/paste still working.

  3. #3
    Join Date
    Aug 2006
    Posts
    227

    Re: How to disable Copy and Paste features in MS Excel

    Hey thank you very much it works in both excel 2003 and 2007
    I do to dead flowers what people at morgues do to dead people. Suck all the moisture out, dip them in plastic, paint them up pretty and put them in a nice frame.

  4. #4
    Join Date
    Apr 2009
    Posts
    2

    Re: How to disable Copy and Paste features in MS Excel

    The above code does not work completely in 2007. While it disables all menu bar instances and all pop up menu instances and all keyboard short cuts (Note that [Ctrl]-x is missing), it does not disable the clipboard in the home tab of the ribbon. Users can still copy, cut and paste by using the clipboard in the ribbon which is the most prominate method available in xl2007.

    Most of the time I find I do not want to disable copy and paste, only cut. This is to prevent users from inadvertantly changing the references for formulas in protected cells or breaking formulas by overwriting the reference cells for them with a cut and paste or drag and drop. It is OK (and expected) if users copy and paste in unprotected cells because this will not drag the references for any formulas along with it. I have found the following code inserted in the "This workbook" module accomplishes this. While it does not technicaly "disable" the cut function, it does prevent the cut and paste from being implemented by canceling the operation on selection of the paste to cell. This works for the xl2007 ribbon clipboard as well. This could also be used to "disable" copy as well by inserting the appropriate cancel code under the Case Is = xlCopy command

    The second series of routines disable drag and drop mode on open or activation and reenables it on close or deactivation as in the code above.

    Option Explicit
    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
    ByVal Target As Excel.Range)

    Select Case Application.CutCopyMode
    Case Is = False
    'do nothing
    Case Is = xlCopy
    'do nothing
    Case Is = xlCut
    MsgBox "Please DO NOT Cut and Paste. Use Copy and Paste; then delete souce."
    Application.CutCopyMode = False 'clear clipboard and cancel cut
    End Select

    End Sub

    Private Sub Workbook_Activate()
    Application.CellDragAndDrop = False
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.CellDragAndDrop = True
    End Sub

    Private Sub Workbook_Deactivate()
    Application.CellDragAndDrop = True
    End Sub

    Private Sub Workbook_Open()
    Application.CellDragAndDrop = False
    End Sub

    Hope this helps others who are struggling with this as I was.

  5. #5
    Join Date
    May 2011
    Posts
    7

    Re: How to disable Copy and Paste features in MS Excel

    The code for disabling the cut, copy and paste from the Ribbon did not work for me. Does anyone have another set of code to try?
    Thanks,
    Scott

  6. #6
    Join Date
    Apr 2009
    Posts
    2

    Re: How to disable Copy and Paste features in MS Excel

    The code must be allowed to run when you open the sheet. Make sure that macros are not disabled or that you allow the macro to run when the little ribbon comes up if you have macros set to ask you first.

    Note again that the code above does NOT disable the cut copy paste functions. Users can still click on them and use them to paste from external sources etc. What it does do is the following.

    If a user selects a cell or a range of cells and then selects cut from the ribbon, the cutcopy mode is activated and the selection is highlighted by the moving dots border like normal. Thus the cut appears to function properly. When the user moves to another cell (selection change) the macro detects that the cut mode is activated, displays a message box reminding the user not to cut, clears the cutmode which removes the moving dot highlight around the selection, and clears the clip board of the cut items. This effectively cancels the cut that the user initiated with no change to the work sheet. A selection change is required to activate this so until a selection change is made the cut mode functions normally. I have not tested this going from one worksheet to another or going from a worksheet to an external source. The goal is to prevent inadvertant modification of formulas linked to user input cells within a single sheet and it is not ment to prevent copying of proprietary data etc.

  7. #7
    Join Date
    May 2011
    Posts
    7

    Re: How to disable Copy and Paste features in MS Excel

    It does prevent some copying and transferring methods but not all. It is a step in the right direction. Thanks. I noticed that if I open a new Excel document from the ribbon, I cannot paste the copied cells. But if I open Excel from the computer's desktop (or start menu), macro's are not running on the new worksheet and therefore can be pasted and saved. So, if cut & copy can be eliminated completely, I am in business. Right now, I am just one step closer. Thanks for helping me get one step closer.

  8. #8
    Join Date
    Apr 2008
    Posts
    315

    Re: How to disable Copy and Paste features in MS Excel

    It does prevent some copying and transferring methods but not all. It is a step in the right direction. Thanks. I noticed that if I open a new Excel document from the ribbon, I cannot paste the copied cells. But if I open Excel from the computer's desktop (or start menu), macro's are not running on the new worksheet and therefore can be pasted and saved. So, if cut & copy can be eliminated completely, I am in business. Right now, I am just one step closer. Thanks for helping me get one step closer.
    Here I have little information for you which help you to reach to next step. You can just go to the tools menu and choose protection and put a password. Your spreadsheet will not be distorted except somebody knows the password. But if your worry is somebody will copy the entire sheet or a section and paste it to a new sheet, then they would have access to your formulas etc. It's almost not possible to disable copy/paste. Even though there are a few alternatives that can stop copying, simply disabling macros will bypass this. Copying from Excel into another application or another instance of Excel can simply be done too, and there are no alternatives I know of to stop this.

Similar Threads

  1. Replies: 5
    Last Post: 26-04-2012, 04:16 AM
  2. Disable copy and paste in word
    By Deren in forum Windows Software
    Replies: 4
    Last Post: 27-03-2012, 08:33 PM
  3. How to disable copy paste option in vista
    By Gunter in forum Windows Software
    Replies: 3
    Last Post: 16-12-2011, 01:02 PM
  4. how to disable copy cut paste with active directory?
    By Sasanka Fernando in forum Windows Security
    Replies: 1
    Last Post: 04-03-2011, 10:52 AM
  5. Replies: 0
    Last Post: 18-03-2009, 10:00 PM

Posting Permissions

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