Disable Cut, Copy, Paste

[Sue Gould]

My apologies for using the wrong Forum, but I could not find any other way to ask my question.

A link in Mr Excel took me to code posted on this site by Ken Puls to prevent users cutting or copying and pasting. I copied it and it works brilliantly, but I will have times that I WILL want to copy, cut etc, and wanted to ask Ken if there is an easy way to alternate between disabling and enabling this.

Problem is, apart from the link that brought me here, I cannot find that actual post, or any way to reply to / query it.

I have copied the full post below (hope it was OK to do that) in the hope that if I am unable to get a reply from Ken, some-one else will have the answer.

In short, I want to know, is there an easy way to enable copy cut paste when I need to, and then disable again when done?

Thanks, Sue



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: (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.

[Aussiebear]

I think I have it cleaned up a bit...

[vba]Option Explicit Sub ToggleCutCopyAndPaste(Allow As Boolean)
'Activate/deactivate cut, copy, paste and pastespecial menu items
Application.CellDragAndDrop = Allow
Call EnableMenuItem(21, Allow)
' cut Call EnableMenuItem(19, Allow)
' copyCall EnableMenuItem(22, Allow)
' paste Call EnableMenuItem(755, Allow)
' pastespecial 'Activate/deactivate drag and drop ability
'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 [/vba]

[vba]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 [/vba]

[vba] Sub CutCopyPasteDisabled() 'Inform user that the functions have been disabled
MsgBox "Sorry! Cutting, copying and pasting have been disabled in this workbook!"
End Sub [/vba]
'*** In the ThisWorkbook Module ***
[vba]Option Explicit Private Sub Workbook_Activate()
Call ToggleCutCopyAndPaste(False)
End Sub [/vba]

[vba]Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call ToggleCutCopyAndPaste(True)
End Sub[/vba]

[vba]Private Sub Workbook_Deactivate()
Call ToggleCutCopyAndPaste(True)
End Sub [/vba]

[vba]Private Sub Workbook_Open()
Call ToggleCutCopyAndPaste(False)
End Sub [/vba]
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.

[RonMcK]

Sue,

Don't worry, you're in the right forum and the right place to ask for help with VBA.

A quick way to preserve the pretty formating of your VBA code: either click the VBA button in the edit window or type vba and /vba in square brackets, and then insert (edit > paste or ^v) your VBA code between the vba tags. Your code will appeer in a box like those in Aussiebear's reply.

I found the code you posted in the following Knowledge Base article by Ken Puls:

http://vbaexpress.com/kb/getarticle.php?kb_id=373

(I selected KBase on Menubar, then Search, next set my parameters as "Excel" and "Ken Puls", and clicked search; this was the first article listed.)

I'm working on your question, however, I appear to be thwarted by how VBA does (or doesn't) work on my Mac; I'll retry when I get home this evening.

[Charlize]

Maybe use another shotcutkey like : 'ctrl shift alt c' to enable the copy and paste routines by using a boolean. When pressed again (ctrl shift alt c) the boolean becomes false and thus, no copy, paste ... allowed.

Just an idea.

Charlize

[Sue Gould]

Thanks all 3 for your responses. Have got side tracked onto an urgent problem, so will read through and trial your ideas as soon as I get the problem sorted and get back to you. Thanks.

[mikerickson]

In a normal module
[VBA]Public CutCopyOK As Boolean

Sub toggleCutCopyAccess()
CutCopyOK = Not (CutCopyOK)
End Sub
[/VBA]

In ThisWorkbook's code module
[VBA]Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Not (CutCopyOK) Then Application.CutCopyMode = False
End Sub
[/VBA]

This won't prevent the user from copying a range of cells and PasteSpecial onto that original range.