Disable Cut, Copy, Paste Request

[TheRealBigB]

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]

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]

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]

Did you put the routines in the correct places ?

Cannot chk it as i do not have excel 2010.

Rob

[TheRealBigB]

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]

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]

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?