Excel

Disable Cut, Copy, Paste

Ease of Use

Easy

Version tested with

2000, 2003, 2007 

Submitted by:

Ken Puls

Description:

It may be desirable to prevent users from cutting, copying, or pasting data into or from your workbook. This code disables all of those functions. 

Discussion:

This macro disables the menu items and keyboard shortcuts for cut, copy, paste, pastespecial, and drag and drop functionality. It will work only in the workbook that you copy the code into, since it is deactivated when the workbook is closed or when it is deactivated. Caveat: It must have macros enabled to run. One method of forcing macros to run can be found at: http://www.vbaexpress.com/kb/getarticle.php?kb_id=379 (Note: Thanks to Bob Phillips (xld) for making this 2000 compliant.) 

Code:

instructions for use

			

'*** In a standard module *** Option Explicit Sub ToggleCutCopyAndPaste(Allow As Boolean) 'Activate/deactivate cut, copy, paste and pastespecial menu items Call EnableMenuItem(21, Allow) ' cut Call EnableMenuItem(19, Allow) ' copy Call EnableMenuItem(22, Allow) ' paste Call EnableMenuItem(755, Allow) ' pastespecial 'Activate/deactivate drag and drop ability Application.CellDragAndDrop = Allow 'Activate/deactivate cut, copy, paste and pastespecial shortcut keys With Application Select Case Allow Case Is = False .OnKey "^c", "CutCopyPasteDisabled" .OnKey "^v", "CutCopyPasteDisabled" .OnKey "^x", "CutCopyPasteDisabled" .OnKey "+{DEL}", "CutCopyPasteDisabled" .OnKey "^{INSERT}", "CutCopyPasteDisabled" Case Is = True .OnKey "^c" .OnKey "^v" .OnKey "^x" .OnKey "+{DEL}" .OnKey "^{INSERT}" End Select End With End Sub Sub EnableMenuItem(ctlId As Integer, Enabled As Boolean) 'Activate/Deactivate specific menu item Dim cBar As CommandBar Dim cBarCtrl As CommandBarControl For Each cBar In Application.CommandBars If cBar.Name <> "Clipboard" Then Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True) If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled End If Next End Sub Sub CutCopyPasteDisabled() 'Inform user that the functions have been disabled MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!" End Sub '*** In the ThisWorkbook Module *** Option Explicit Private Sub Workbook_Activate() Call ToggleCutCopyAndPaste(False) End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call ToggleCutCopyAndPaste(True) End Sub Private Sub Workbook_Deactivate() Call ToggleCutCopyAndPaste(True) End Sub Private Sub Workbook_Open() Call ToggleCutCopyAndPaste(False) End Sub

How to use:

  1. Copy above code for the standard module.
  2. In Excel press Alt + F11 to enter the VBE.
  3. Press Ctrl + R to show the Project Explorer.
  4. Right-click desired file on left (in bold).
  5. Choose Insert -> Module.
  6. Paste code into the right pane.
  7. Copy the code for the ThisWorkbook module.
  8. In the project explorer, locate the ThisWorkbook object.
  9. Double click the ThisWorkbook object.
  10. Paste code into the right pane.
  11. Press Alt + Q to close the VBE.
  12. Save workbook before any other changes.
  13. Close and reopen the workbook.
 

Test the code:

  1. Try any combination of cutting, copying or pasting in whatever means you prefer.
 

Sample File:

DisableCopyPaste.zip 9.79KB 

Approved by mdmackillop


This entry has been viewed 919 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express