View Full Version : Disable Cut, Copy, Paste Request
TheRealBigB
04-12-2011, 05:16 PM
Is there any chance that this very helpfuly posting could be updated to work with Excel 2010, in particular to disable to Home Menu / Clipboard functionality of Copy and Paste?
kb_id=373
Rob342
04-14-2011, 09:39 AM
Hi
Try this works for me in excel 2007 should work in 2010
'*** In the ThisWorkbook Module ***
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_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox ("The 'Save As' Function has been disabled."), vbInformation, "Save As Disabled'"
Cancel = True
End If
End Sub
In a Standard Module
Option Explicit
Option Private Module
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 Has Been Disabled In This Workbook!"
End Sub
ps dont forget to call the routine to enable, before closing the workbook
TheRealBigB
04-14-2011, 05:07 PM
Thanks for your response Rob...It still doesn't seem to disable the Clipboard on the Ribbon in Excel 2010. Maybe I didn't use it correctly.
:banghead:
Rob342
04-15-2011, 05:09 AM
Did you put the routines in the correct places ?
Cannot chk it as i do not have excel 2010.
Rob
TheRealBigB
04-15-2011, 09:14 AM
Did you put the routines in the correct places ?
Cannot chk it as i do not have excel 2010.
Rob
I believe so. At first, the right click context menu items are disabled, but the paste function there changes if you use it AFTER using the Clipboard functions from the ribbon which were never disabled. At first Paste was disabled, but then Paste Options appear. The Paste options and functions change again with additional use. Strange behavior. :dunno
Thanks for the effort. I suppose someone with Excel 2010 would be needed to take a look at this.
Rob342
04-16-2011, 04:24 AM
Hi
Have tried this routine again and you are right it does not disable the ribbon, i have looked all around trying to find the routine for the ribbon but to no avail.
Maybe MD, Simon or XLD can point you in the right direction.
Rob
TheRealBigB
04-18-2011, 08:55 AM
Hi
Have tried this routine again and you are right it does not disable the ribbon, i have looked all around trying to find the routine for the ribbon but to no avail.
Maybe MD, Simon or XLD can point you in the right direction.
Rob
Thanks again Rob...I searched online as well to no avail. Is it possible that Excel 2010 is still so new that no one tried this yet? Or maybe disabling functions on Ribbons cannot be done?
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.