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

29 comments:

  1. 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. 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. 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
  4. 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
  5. 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
  6. 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
  7. 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
  8. 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
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. Hi Frederic

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

    Regards

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

    ReplyDelete
  17. 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
  18. Hi Bharat

    I do not have solution for conversion in Gujarati.

    Regards//Yogesh Gupta

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

    ReplyDelete
  20. 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
  21. 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
  22. 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
  23. 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
  24. Good code...but does not block the Ribbon. Found complicated XML code for this. Any other way?

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

    ReplyDelete
  26. 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
  27. 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
  28. 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