Tuesday, April 14, 2009

Macros in Excel : Disable Cut Copy Paste


Many times we need to disable Cut Copy and Paste in our workbook. You can achieve this by using Marcos in Excel. You need to add the Macro code to This workbook section in your VBA project. When you go to your Visual Basic Editor you can see the ThisWorkbook on the left hand side of your VBA Project.

You need to double click on highlighed portion and copy the following code. This code also disables the rightclick menu. Since these macros has Private word before Sub, no one will be able to see these macros from View Macro menu.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
With Application
.CellDragAndDrop = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
.CutCopyMode = False
End With
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=19) 'copy
    Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=21) ' Cut
    Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=22) ' Paste
    Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=755) ' Paste Special
    Ctrl.Enabled = True
Next Ctrl

End Sub

Private Sub Workbook_Open()
    On Error Resume Next
    With Application
    .CutCopyMode = False
    .CellDragAndDrop = False
    .OnKey "^c", ""
    .OnKey "^v", ""
    .OnKey "^x", ""
    .OnKey "+{DEL}", ""
    .OnKey "^{INSERT}", ""
    End With
   
    Dim Ctrl As Office.CommandBarControl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=19) ' Copy
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=21) ' Cut
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=22) ' Paste
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=755) ' Paste Special
        Ctrl.Enabled = False
    Next Ctrl
    
    End Sub

Private Sub Workbook_Activate()
    On Error Resume Next
    With Application
    .CutCopyMode = False
    .CellDragAndDrop = False
    .OnKey "^c", ""
    .OnKey "^v", ""
    .OnKey "^x", ""
    .OnKey "+{DEL}", ""
    .OnKey "^{INSERT}", ""
    End With
    Dim Ctrl As Office.CommandBarControl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=19) ' Copy
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=21) ' Cut
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=22) ' Paste
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=755) ' Paste Special
        Ctrl.Enabled = False
    Next Ctrl
   
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click menu deactivated." & vbCrLf & _
"For this file:", 16, ""
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
     Application.CutCopyMode = False
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    
    On Error Resume Next
    With Application
    .CutCopyMode = False
    .CellDragAndDrop = False
    .OnKey "^c", ""
    .OnKey "^v", ""
    .OnKey "^x", ""
    .OnKey "+{DEL}", ""
    .OnKey "^{INSERT}", ""
    End With
    Dim Ctrl As Office.CommandBarControl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=19) ' Copy
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=21) ' Cut
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=22) ' Paste
        Ctrl.Enabled = False
    Next Ctrl
    For Each Ctrl In Application.CommandBars.FindControls(ID:=755) ' Paste Special
        Ctrl.Enabled = False
    Next Ctrl
    
End Sub
Private Sub Workbook_Deactivate()
On Error Resume Next
With Application
.CellDragAndDrop = True
.OnKey "^c"
.OnKey "^v"
.OnKey "^x"
.OnKey "+{DEL}"
.OnKey "^{INSERT}"
.CutCopyMode = False
End With
Dim Ctrl As Office.CommandBarControl
For Each Ctrl In Application.CommandBars.FindControls(ID:=19) ' Copy
    Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=21) ' Cut
    Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=22) ' Paste
    Ctrl.Enabled = True
Next Ctrl
For Each Ctrl In Application.CommandBars.FindControls(ID:=755) ' Paste Special
    Ctrl.Enabled = True
Next Ctrl

End Sub

Download file having disable cut copy paste Macro
Posted by Yogesh Gupta at 7:32 PM
Labels: , ,

35 comments:

  1. UnknownMay 11, 2009 at 3:11 PM

    Great attempt! However it also fails the critical last test ie if you double click in cell and select text, then ctrl-c, you can then go to another cell and double click and ctrl-v. Voila - you can copy/paste.

    Good work though

    ReplyDelete
  2. UnknownSeptember 11, 2009 at 2:43 PM

    Nice this Macro. I'd like to use it in one of my excel files. But I only want to disable cut. So that copy and paste is possible. Has anyone ideas on how to change this macro for that purpouse? I tried several thing but so far it is not working..

    ReplyDelete
  3. Yogesh GuptaSeptember 11, 2009 at 3:39 PM

    Hello Cyril

    You can download file having only cut disabled

    ReplyDelete
  4. UnknownSeptember 17, 2009 at 12:33 PM

    Thanks for the reply. I only have one problem. I played a bit with the first macro and now copy paste and past special are greyed out in all my excel documents. I don't see any macro's. Any Ideas on how to get it back? I can copy and paste using ctrl c and ctrl v

    ReplyDelete
  5. Yogesh GuptaSeptember 17, 2009 at 12:49 PM

    Hello Cyril
    It seems that you have disabled the code to enable those menu items. That is the reason you are not getting them highlighted when you file is closed / deactivated.

    You need to use the full macro code once to get it back. You can also down load disable cut copy paste file. Enable macros and just open and close this file.

    ReplyDelete
  6. UnknownSeptember 17, 2009 at 1:26 PM

    Thanks for the quick answer, I already googled it and used this soltion:

    Open excel
    hit alt-f11 (to get to the VBE)
    hit ctrl-g (to see the immediate window)

    type this and hit enter:
    application.commandbars("cell").reset

    ReplyDelete
  7. UnknownOctober 7, 2009 at 1:11 PM

    Thanks for the disable cut xls file. I have only one problem with it. If I want to copy someting from another excel file into the 'disable cut xls file' I cannot click the paste button. I can copy from other sources but not from another excel file.. If that is fixed really would make me very happy! I need to learn more about macro's /programming, because I have no idea about how to change this myself..

    ReplyDelete
  8. Yogesh GuptaOctober 7, 2009 at 3:15 PM

    Hello Cyril

    I have updated the file, now it will let you copy from another excel file. However the Cut will remain disabled in excel until the file with this macro is open.

    Downlaod diable cut.xls . This is updated on the ealier link also.

    ReplyDelete
  9. UnknownOctober 21, 2009 at 1:59 PM

    Hello yogesh Gupta, copy from other document works very good, thanks! Only thing that doesn't work is copy cells by dragging with the mouse(like 1,2,3 and copy further to 4,5,6,etc, I don't know the name in english of this function). Hope this is not to difficult to also allow?

    ReplyDelete
  10. Yogesh GuptaOctober 21, 2009 at 5:54 PM

    Hello Cyril
    This function works with dragging and we have disabled cell dragging. Since you want to disable cut, we need to disable dragging also.

    If you enable dragging, user can actually do cut by dragging the cells to another location.

    You can achieve your desired action by using Fill option. This will give 1,2,3 then fill further cells by 4,5,6 based on the range you have selected.

    ReplyDelete
  11. UnknownOctober 21, 2009 at 6:22 PM

    Thanks for the quick respons! I now understand the macro much better. I just changed the dragging part to true. Now rightmouseclick and cut and also ctrl x are disabled but dragging is enabled. It is not that important for me that cutting is not completly disabled.

    ReplyDelete
  12. GaUrAvFebruary 25, 2010 at 12:37 PM

    Hello sir
    i want to disable the SAVE, when we close our file. means i cud write any word , but when it comes to save my file doesnt save the new matter or dont ask me to save the new one. and sir i ve made a list of company names with vat pan no. now i want to arrange that file alphabatic wise. how it will be done? Please help.

    ReplyDelete
  13. Yogesh GuptaFebruary 25, 2010 at 9:36 PM

    Hello Gaurav

    For closing file without saving you need to copy following macro to Thisworkbook section.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    ActiveWorkbook.Close savechanges:=False

    End Sub

    Your second requirement of arranging list alphabatic can be achieved through sort ranges.

    Regards//Yogesh Gupta

    ReplyDelete
  14. GaUrAvFebruary 26, 2010 at 4:02 PM

    Hello Sir,
    i copyed the macro code . but sir when i close only exel file. nothing happen. its doesnt close/ show any thing but when i close the complete folder it still asking me to save or not
    . where is this SORT RANGES option in exel file?
    Kind Regards
    Gaurav

    ReplyDelete
  15. rascasse83July 13, 2010 at 8:57 PM

    Hi Yogesh thanks for your posts, the updated version from your post dated October 7, 2009 3:15 PM, which copies data "accross workbooks" is no longer available on the downloadable website
    http://www.4shared.com/file/131881714/94c0f510/disable_cut.html
    Could you post it on your own website ?
    I have tried all sorts to try to copy accross workbooks and even tried commenting out the
    .CutCopyMode = False
    but it didn't solve the issue.
    thanks
    Frederic

    ReplyDelete
  16. Yogesh GuptaJuly 15, 2010 at 11:25 PM

    Hi Frederic

    You can download file with disable cut facility from download section of my blog.

    Regards

    ReplyDelete
  17. bharatJuly 21, 2010 at 11:09 PM

    જેમ અંગ્રેજીના નંબરનું અંગ્રેજીમાં શબ્દોમાં કન્વર્ટ થાય છે. તેમ અંગ્રેજીના નંબરોનું ગુજરાતીમાં કન્વર્ટ ન થાય ?
    મારે પગાર બીલ અને અન્ય ફાઇલોમાં આ ફંક્શનની ખૂબ જ જરૂર પડે છે.

    ReplyDelete
  18. bharatJuly 22, 2010 at 9:31 PM

    Jis tarah english number ka english me convert hota hai aisa gujarati me nahi ho sakta

    Ye Function mere ko bahut jarurat padti hai account aur salary jaisi detail me manualy likhana padta hai so help me the function

    ReplyDelete
  19. Yogesh GuptaJuly 24, 2010 at 9:13 AM

    Hi Bharat

    I do not have solution for conversion in Gujarati.

    Regards//Yogesh Gupta

    ReplyDelete
  20. bharatJuly 27, 2010 at 5:27 PM

    Excel me user form to creat ho gaya hai par us user form pe save button rakhana hai
    So Please help

    ReplyDelete
  21. UnknownNovember 5, 2010 at 9:24 PM

    thanks a lot from for ur reply to all the above,sir i need ur help in disbling copy,save of any xls,file as i don't want some body copy my file to any other computer but if i gave him he can edit that( he should n't gave to other)

    ReplyDelete
  22. FrankMarch 15, 2012 at 11:12 PM

    Yogesh et al.,

    This is a fantastic code! Is there any way to modify it so that everything else is the same, but "paste special -> values" is allowed?

    Thanks in advance!

    ReplyDelete
  23. Leejoh mathewMay 4, 2012 at 4:16 PM

    Hi Yogesh,

    I saw one of your post related to disabling save. Can you advise what needs to be done for disabling both Save and Save As?

    Regards,
    Leejoh

    ReplyDelete
  24. Leejoh mathewMay 4, 2012 at 8:02 PM

    Hi Yogesh,

    Another question.

    The macro that you have provided for disable copy, paste is working good but the toolbar copy option is allowing copying

    ReplyDelete
  25. UnknownOctober 5, 2012 at 12:09 AM

    Good code...but does not block the Ribbon. Found complicated XML code for this. Any other way?

    ReplyDelete
  26. UnknownOctober 5, 2012 at 12:09 AM

    Good code...but does not block the Ribbon. Found complicated XML code for this. Any other way?

    ReplyDelete
  27. Santosh ChaskarOctober 31, 2012 at 12:21 AM

    Hi Yogesh,

    I had download your this disable Copy and paste excel sheet and just open. after that when i open new workbook all cope and paste option disappear. I cant drag formulla also. i dont want use this function new workbook.

    please reply me. how to enable copy and paste function?


    thanks in advance

    santosh chaskar

    ReplyDelete
    Replies
    1. UnknownAugust 31, 2015 at 3:51 PM

      your script change and write true instead of false..............

      Delete
  28. marriagealbumMarch 3, 2013 at 10:10 PM

    Hi Is there anyone who can help me out. I disabled the copy/cut/paste option using a macro (Whichi i will copy at last of my content). i applied the code to entire workbook. Now i want copy/cut/past option in one worksheet and rest should do the same. can anyone come with idea. Thank RVR.

    My code:


    Option Explicit

    Sub EnableControl(Id As Integer, Enabled As Boolean)
    Dim CB As CommandBar
    Dim C As CommandBarControl
    For Each CB In Application.CommandBars
    Set C = CB.FindControl(Id:=Id, recursive:=True)
    If Not C Is Nothing Then C.Enabled = Enabled
    Next
    End Sub

    Private Sub Workbook_Activate()
    EnableControl 21, False ' cut
    EnableControl 19, False ' copy
    EnableControl 22, False ' paste
    EnableControl 755, False ' pastespecial
    Application.OnKey "^c", ""
    Application.OnKey "^v", ""
    Application.OnKey "+{DEL}", ""
    Application.OnKey "+{INSERT}", ""
    Application.CellDragAndDrop = False
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    EnableControl 21, True ' cut
    EnableControl 19, True ' copy
    EnableControl 22, True ' paste
    EnableControl 755, True ' pastespecial
    Application.OnKey "^c"
    Application.OnKey "^v"
    Application.OnKey "+{DEL}"
    Application.OnKey "+{INSERT}"
    Application.CellDragAndDrop = True
    End Sub

    Private Sub Workbook_Deactivate()
    EnableControl 21, True ' cut
    EnableControl 19, True ' copy
    EnableControl 22, True ' paste
    EnableControl 755, True ' pastespecial
    Application.OnKey "^c"
    Application.OnKey "^v"
    Application.OnKey "+{DEL}"
    Application.OnKey "+{INSERT}"
    Application.CellDragAndDrop = True
    End Sub

    Private Sub Workbook_Open()
    EnableControl 21, False ' cut
    EnableControl 19, False ' copy
    EnableControl 22, False ' paste
    EnableControl 755, False ' pastespecial
    Application.OnKey "^c", ""
    Application.OnKey "^v", ""
    Application.OnKey "+{DEL}", ""
    Application.OnKey "+{INSERT}", ""
    Application.CellDragAndDrop = False
    End Sub

    Private Sub ClearBorders()
    Range("A1:K1000").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("L14").Select
    End Sub



    ReplyDelete
    Replies
    1. UnknownAugust 31, 2015 at 3:47 PM

      simple....first u find perticular sheet then if ststement apply............

      Delete
  29. UnknownDecember 5, 2014 at 10:44 PM

    Can you disable if you double click in cell and select text, then ctrl-c, you can then go to another cell and double click and ctrl-v. Voila - you can copy/paste.

    ReplyDelete
  30. StRiKeRFebruary 25, 2015 at 7:20 PM

    Paste is disabled at menu. How can I also disable cut and copy at menu?

    ReplyDelete
  31. UnknownAugust 7, 2015 at 4:55 PM

    Hi Yogesh, 2 problems!

    1. copy and paste still works as per comment from snagger1.all you have to do is double click and select the text and then double click into another cell and paste

    2. Even though you have successfully restricted copy and paste with the excel file or from other excel files, it is still possible to copy and paste comments/numbers from word documents/email etc !

    Would be very grateful if you could come up with a solution to this :-)

    ReplyDelete
  32. UnknownFebruary 1, 2016 at 11:18 AM

    Hi Yogesh,

    I want macro code to disable cut/copy/paste option in particular column of worksheet.
    Can you please help in this.

    Thanks in advance.

    ReplyDelete
  33. योगेशMay 6, 2016 at 5:51 PM

    Sir,

    This did not work in Excel 2007

    Any idea, why?

    YB

    ReplyDelete

Subscribe to: Post Comments (Atom)