Go Back   TechArena Community > Software > Tips & Tweaks
Become a Member!
Forgot your username/password?
Register Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



How to disable Copy and Paste features in MS Excel

Tips & Tweaks


Reply
 
Thread Tools Search this Thread
  #1  
Old 18-11-2008
Member
 
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.
Reply With Quote
  #2  
Old 18-11-2008
Member
 
Join Date: Apr 2008
Posts: 436
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:

Quote:
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.
Reply With Quote
  #3  
Old 18-11-2008
Member
 
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.
Reply With Quote
  #4  
Old 02-04-2009
Member
 
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.
Reply With Quote
  #5  
Old 02-05-2011
Member
 
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
Reply With Quote
  #6  
Old 04-05-2011
Member
 
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.
Reply With Quote
  #7  
Old 05-05-2011
Member
 
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.
Reply With Quote
  #8  
Old 05-05-2011
Member
 
Join Date: Apr 2008
Posts: 315
Re: How to disable Copy and Paste features in MS Excel

Quote:
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.
Reply With Quote
Reply

  TechArena Community > Software > Tips & Tweaks
Tags: , , , , , , ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "How to disable Copy and Paste features in MS Excel"
Thread Thread Starter Forum Replies Last Post
I cannot do Copy and Paste or Paste Special between Excel Workbooks Acca-OR Windows Software 5 26-04-2012 04:16 AM
Disable copy and paste in word Deren Windows Software 4 27-03-2012 08:33 PM
How to disable copy paste option in vista Gunter Windows Software 3 16-12-2011 01:02 PM
how to disable copy cut paste with active directory? Sasanka Fernando Windows Security 1 04-03-2011 10:52 AM
Copy Text from One Cell to Another in Microsoft Excel without copy & paste Computer_Freak Tips & Tweaks 0 18-03-2009 10:00 PM


All times are GMT +5.5. The time now is 06:05 AM.