Disable Copy Paste

[ptolos]

I've used the code located in article 373 (/kb/getarticle.php?kb_id=373) but it does not disable the "Clipboard" menu at the top of Excel 2007. All other copy/cut/paste features are in fact disabled with this macro, except for the toolbar features ("Home" tab, "Clipboard" section). Is there a way to disable this workaround?

[mikerickson]

Perhaps something like this in a sheet's code module. Or in the ThisWorkbook SheetSelectionChange event.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Dim xDat As New DataObject
    Application.CutCopyMode = False
    With xDat
        .SetText "Not Allowed"
        .PutInClipboard
    End With
End Sub

[Simon Lloyd]

You can try the attached!

[ptolos]

I've tried both of these solutions, and neither limit the cut/copy/paste feature from the toolbar at the top of Excel.

[Simon Lloyd]

Mine definately does!, which version of Excel are you using? have you got macro's enabled?

I suspect that macro's are disabled, the fact that you can write code, view it or change it is not an indicator that they are enabled!

[ptolos]

Thanks for the follow-up, Simon. I've definitely enabled macros, and the code disabled the right click drop down options for copy, cut and paste as well as the keyboard shortcuts for this as well. In the file you attached, however, I am still able to use the menu to copy and paste from cell a1 to cell b1, for example.

[Simon Lloyd]

Well all i can suggest is that you go to the Thisworkbook module run the workbooks open event, or activate a different worksheet and then try, because in the example i gave all copy/pastes have been disabled, i downloaded my example and the copy cut etc. is greyed out when you try to use it!

[ptolos]

I'm running 2007 and I manually ran the workbooks open event and took a look at the code and stepped through the process. It perpetually loops in the red part of the following code:

[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 ' Begin loop
Set cBarCtrl = cBar.FindControl(ID:=ctlId, recursive:=True)
If Not cBarCtrl Is Nothing Then cBarCtrl.Enabled = Enabled
End If
Next ' End loop
End Sub[/vba]