Macro to turn off cut and paste?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,194
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,
I have a document that we use as an input document.
All people have to do is Type in the amounts etc into the correct cells. everything is protected and setup nicely so you would think this is fine, but NO.
Some people insist on breaking it! the latest way is with cutting and pasting.
is there any way I can switch off the ability to cut and paste with a macro or something?

any ideas will help.
p.s. before you say it, I didn't think they could break it this way either but they do! lol :)

Thanks

Tony
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Not my code but some I keep handy for similar reasons...

(Apologies to the OP if they see this but I don't seem to have kept the link I found this on either)

In a normal module put the following code;

Code:
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

Then in the "This Workbook" module put this code;

Code:
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

You'll need to save the Workbook and then reopen it for it to work.
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top