Sue Gould
01-23-2008, 05:21 PM
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:
Copy above code for the standard module.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Copy the code for the ThisWorkbook module.
In the project explorer, locate the ThisWorkbook object.
Double click the ThisWorkbook object.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.
Close and reopen the workbook.Test the code:
Try any combination of cutting, copying or pasting in whatever means you prefer.
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:
Copy above code for the standard module.
In Excel press Alt + F11 to enter the VBE.
Press Ctrl + R to show the Project Explorer.
Right-click desired file on left (in bold).
Choose Insert -> Module.
Paste code into the right pane.
Copy the code for the ThisWorkbook module.
In the project explorer, locate the ThisWorkbook object.
Double click the ThisWorkbook object.
Paste code into the right pane.
Press Alt + Q to close the VBE.
Save workbook before any other changes.
Close and reopen the workbook.Test the code:
Try any combination of cutting, copying or pasting in whatever means you prefer.