Hi friends , is it possible to disable copy and paste commands in excel 2003 and 2007 ?
Printable View
Hi friends , is it possible to disable copy and paste commands in excel 2003 and 2007 ?
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.
Hey thank you very much it works in both excel 2003 and 2007
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.
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
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.
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.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.