Disable Cut, Copy, Paste Macro not working

naiarasward

naiarasward

New member
Joined
May 16, 2016
Messages
14
Reaction score
0
Points
0
Hi,

Hi I am using one of your macros to disable cut, copy and paste. Works great if I am trying to do that through shortcuts (ctrl+c, ctrl+v, ctrl+x) but if I click on the menu buttons on the ribbon then I can still copy paste.

I want the user to be able to type into some cells. Only type. I can protect the sheet and stuff but I need them to not be able to copy, cut, paste OR use the format painter in any way so I can be sure my conditional formatting is safe.
Here is the code:

Code: 
 '*** 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
Help with that please?

 
Okay, so the issue isn't with the macro, it's with the Ribbon user interface.

To fix this, you'll need to download and install the CustomUI editor, then:
  • Save & Close your workbook
  • Open the CustomUI Editor
  • Open your workbook within the CustomUI Editor
  • Paste in the following code

Code: 
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">	<ribbon>
		<tabs>
			<tab idMso="TabHome">
				<group idMso="GroupClipboard" visible="false" />
			</tab>
		</tabs>
	</ribbon>
</customUI>

Save it, close it and re-open the Excel file. You should find that the whole clipboard group is gone. (It will come back for any other file.)
 
Thank you for the quick response. but it didn't work.
 
This file was created for 2013. When you open it, can you see the Clipboard group on the Home tab? (The answer should be no.)
 

Attachments

  • Book1.xlsx
    8.1 KB · Views: 122
I can see the clipboard group still. One thing: trying to fix this before I deleted the clipboard group so I recreated. So, the clipboard group is a custom group now. Is this the problem?
 
Is there a way to recover my original clipboard group?
 
Ok. Recovered the original Clipboard group. And now I can't see it.
 
So there is no way a user can put that back unless you change the code right?
 
Correct. That code we added overrides the user interface for that file only. As soon as the user selects another workbook the group will reappear, when they select this workbook again, it goes away.

If they were really tenacious, they could modify their ribbon and add individual commands for copy/paste. That's unlikely, but if you think it would be an issue, you could always disable those specific commands as well. The XML code for this would be as follows:

Code: 
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
        <commands>
            <command idMso="Cut" enabled="false"/>
            <command idMso="Copy" enabled="false"/>
            <command idMso="CopySplitButton" enabled="false"/>
            <command idMso="CopyAsPicture" enabled="false"/>
            <command idMso="Paste" enabled="false"/>
            <command idMso="PasteMenu" enabled="false"/>
            <command idMso="PasteGallery" enabled="false"/>
            <command idMso="PasteSpecialDialog" enabled="false"/>
            <command idMso="FormatPainter" enabled="false"/>
        </commands>
</customUI>

That would basically grey out the commands, but leave them showing on the tab. If you want to hide the group (like we did previously), you'd go with this:

Code: 
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
        <commands>
            <command idMso="Cut" enabled="false"/>
            <command idMso="Copy" enabled="false"/>
            <command idMso="CopySplitButton" enabled="false"/>
            <command idMso="CopyAsPicture" enabled="false"/>
            <command idMso="Paste" enabled="false"/>
            <command idMso="PasteMenu" enabled="false"/>
            <command idMso="PasteGallery" enabled="false"/>
            <command idMso="PasteSpecialDialog" enabled="false"/>
            <command idMso="FormatPainter" enabled="false"/>
        </commands>
    <ribbon>
        <tabs>
            <tab idMso="TabHome">
                <group idMso="GroupClipboard" visible="false" />
            </tab>
        </tabs>
    </ribbon>

</customUI>
 
All this is awesome. You were super helpful. Thank you so much.
 
For these last 2 codes you sent me, how should I add them? As soon as I paste it it all turns red and doesn't work. Am I doing something wrong?
 
Never mind. I see now it is for Custom UI Editor. So I pasted it there and is showing an error. See attachment.
 

Attachments

  • Capture.PNG
    Capture.PNG
    79.4 KB · Views: 47
Hey there,

You can only have one set of <customui> tags in the document. if you wan to use both pieces, then replace the entire set of XML in the document with the last example (only) that I provided.
 
You must log in or register to reply here.
Back
Top