提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何在Excel中禁用剪切,复制和粘贴功能?

假设你有一个重要数据的工作簿,你需要防止被剪切,复制和粘贴。 如何实现它? 本文提供了一种VBA方法,可以在Excel工作簿中同时禁用剪切,复制和粘贴功能。

使用VBA代码禁用剪切,复制和粘贴功能

Office选项卡在Office中启用选项卡式编辑和浏览,使您的工作更轻松......
Kutools for Excel - 最佳办公生产力工具将解决您的大部分Excel问题
  • 重用任何东西: 将最常用或最复杂的公式,图表和其他任何内容添加到您的收藏夹中,并在将来快速重复使用它们。
  • 超过20文本功能: 从文本字符串中提取数字; 提取或删除部分文本; 将数字和货币转换为英语单词...
  • 合并工具:多个工作簿和表格合二为一; 合并多个单元格/行/列而不丢失数据; 合并重复行和总和...
  • 拆分工具:根据价值将数据拆分为多个表格; 一个工作簿到多个Excel,PDF或CSV文件; 一列到多列......
  • 粘贴跳过 隐藏/过滤行; 数和总和 按背景颜色; 创建邮件列表和 通过Cell的价值发送电子邮件...
  • 超级过滤器: 创建高级过滤方案并应用于任何工作表; 排序 按周,日,频率等; 筛选 通过大胆,公式,评论......
  • 超过300强大的功能; 适用于Office 2007-2019和365; 支持所有语言; 在公司轻松部署; 全功能60天免费试用。

箭头蓝色右泡使用VBA代码禁用剪切,复制和粘贴功能


请按照以下步骤在Excel工作簿中禁用剪切,复制和粘贴功能。

1。 在工作簿中,您需要禁用剪切,复制和粘贴功能,请按 其他 + F11 键同时打开 Microsoft Visual Basic for Applications 窗口。

2。 在里面 Microsoft Visual Basic for Applications 窗口,请双击 的ThisWorkbook 在左边 项目 窗格,然后将下面的VBA代码复制并粘贴到 ThisWorkbook(Code) 窗口。 看截图:

VBA代码:在Excel中同时禁用剪切,复制和粘贴功能

Private Sub Workbook_Activate()
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
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)
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub

3。 那么请按下 其他 + Q 键退出 Microsoft Visual Basic for Applications 窗口。

现在,您无法从此工作簿中剪切或复制数据,同时,从其他工作表或工作簿中复制的数据无法粘贴到此工作簿中。

注意:运行上述VBA代码后,拖放功能也被禁用。


箭头蓝色右泡相关文章:


Kutools for Excel - 最佳办公生产力工具提高80%的生产力

  • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
  • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
  • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
  • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
  • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
  • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
  • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
  • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
  • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
kte tab 201905

Office选项卡为Office提供选项卡式界面,使您的工作更轻松

  • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
  • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
  • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    RAHUL MODI · 2 months ago
    THANKS SIR
  • To post as a guest, your comment is unpublished.
    RN PK · 5 months ago
    Thanks it works perfect ....can it be applied on a range only somehow?
  • To post as a guest, your comment is unpublished.
    Sam · 7 months ago
    It really works well, thanks a lot.
  • To post as a guest, your comment is unpublished.
    Vic · 7 months ago
    Hi, the code works well thanks.
    However, the read only option was enabled. (if you don't want to modify). If I set ..File, save as, tools, general options, read only recommended.. then the code does not work.

    Thanks in advance.
  • To post as a guest, your comment is unpublished.
    Al Hammad · 11 months ago
    Thank your for your direction.I am Trying in office 2013 ,but nothing changed.
  • To post as a guest, your comment is unpublished.
    Garak0410 · 1 years ago
    Was super glad to find this post but I need a little help in limiting this to a single worksheet in a multi-worksheet Workbook. And need to know how to execute this without user intervention...we need this ASAP on a workbook to help prevent errors.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      The following VBA code can help you to solve the problem. And don't forget to replace "Sheet2" in the code with your sheet name.

      Public mJWSName As String

      Private Sub Workbook_Open()
      mJWSName = "Sheet2"
      End Sub

      Private Sub Workbook_Activate()
      If ActiveSheet.Name = mJWSName Then
      Application.CutCopyMode = False
      Application.OnKey "^c", ""
      Application.CellDragAndDrop = False
      End If
      End Sub

      Private Sub Workbook_Deactivate()
      Application.OnKey "^c", ""
      Application.CellDragAndDrop = True
      Application.CutCopyMode = False
      End Sub


      Private Sub Workbook_WindowActivate(ByVal Wn As Window)
      If ActiveSheet.Name = mJWSName Then
      Application.CutCopyMode = False
      Application.OnKey "^c", ""
      Application.CellDragAndDrop = False
      End If
      End Sub

      Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
      Application.OnKey "^c"
      Application.CellDragAndDrop = True
      Application.CutCopyMode = False
      End Sub

      Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
      On Error Resume Next
      If Sh.Name = mJWSName Then
      Application.CutCopyMode = False
      End If
      End Sub

      Private Sub Workbook_SheetActivate(ByVal Sh As Object)
      On Error Resume Next
      If Sh.Name = mJWSName Then
      Application.OnKey "^c", ""
      Application.CellDragAndDrop = False
      Application.CutCopyMode = False
      End If
      End Sub

      Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
      Application.OnKey "^c"
      Application.CellDragAndDrop = True
      Application.CutCopyMode = False
      End Sub
      • To post as a guest, your comment is unpublished.
        Garak0410 · 1 years ago
        One last question - Where does this code go and how does it get executed? The project this needs to work with attaches an XLA file to the Excel sheet that contains much of the code. Didn't know if this needs to go into a module or the code behind this sheet. Thanks...
  • To post as a guest, your comment is unpublished.
    Linda · 1 years ago
    Hi

    I´m a beginner of VBA codes and this code solved 99% of my problems.

    Is it possible to lock the hole sheet (as this code dose) but still have one cell unlocked?
    (Clarify, I would like to allow copy past in info in only one cell in the hole sheet).

    Best regards
    L
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hello Linda,
      You can solve the problem without using VBA code.
      Right click the cell and select Format Cells from the context menu, uncheck the Locked box under the Protection tab in the dialog. And then protect the worksheet with password.
  • To post as a guest, your comment is unpublished.
    Carmelo · 1 years ago
    Hi,
    Is it possible to disable "CUT" only? but I could still use the Copy and Paste?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi Carmelo,
      Please copy and paste the below code into the Workbook code window in your workbook and then save it as an Excel Macro-enabled workbook. Then the "Cut" function will be disabled.

      Option Explicit
      Private WithEvents Cmbrs As CommandBars

      Private Sub Workbook_Open()
      Set Cmbrs = Application.CommandBars
      End Sub

      Private Sub Workbook_Activate()
      If Application.CutCopyMode = 2 Then
      Application.CutCopyMode = 0
      End If
      End Sub

      Private Sub Cmbrs_OnUpdate()
      If Me Is ActiveWorkbook Then
      If Application.CutCopyMode = 2 Then
      Application.CutCopyMode = 0
      MsgBox "Cut Operations disabled"
      End If
      End If
      End Sub
      • To post as a guest, your comment is unpublished.
        ERCA · 1 years ago
        I tried just like you said but nothing happends. A want exactly the same, Only the
        "Cut" function disabled. I need to disable the "cut" option from Rightclick buton option and from the toolbar.
        It is posible crystal??
        • To post as a guest, your comment is unpublished.
          crystal · 1 years ago
          Good day,
          The code works well in my case. May I know your Office version?
  • To post as a guest, your comment is unpublished.
    Midas Pvt Ltd · 1 years ago
    Dear,

    i want to disable cut, copy, paste in excel 2007 sheet
    can you share code for this
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      Thank you for your comment. I am trying to figure out the solution for Excel 2007. Please wait patiently.
  • To post as a guest, your comment is unpublished.
    Sky · 1 years ago
    How to enable the cut, copy and paste function back? Please advice!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Sky
      Please run the below VBA1 (place the code in the ThisWorkbook module) to disable the cut, copy and paste function in your workbook.

      VBA1:
      Sub DelCopy()
      With Application
      .OnKey "^x", ""
      .OnKey "^c", ""
      .CommandBars("Cell").Controls(1).Enabled = False
      .CommandBars("Cell").Controls(2).Enabled = False
      End With
      End Sub

      And the run the VBA2 to enable all these functions back to your workbook.

      VBA2:
      Sub RecoverCopy()
      With Application
      .OnKey "^x"
      .OnKey "^c"
      .CommandBars("Cell").Controls(1).Enabled = True
      .CommandBars("Cell").Controls(2).Enabled = True
      End With
      End Sub
      • To post as a guest, your comment is unpublished.
        mags · 1 years ago
        This doesn't seem to have worked for me - should I delete the original VBA code and then paste the above in to enable the Cut, copy and paste function again?
        • To post as a guest, your comment is unpublished.
          crystal · 1 years ago
          Good Day,
          The VBA1in above comment is the replacement of the original code.
  • To post as a guest, your comment is unpublished.
    Martin · 2 years ago
    Thanks a Lot.Its working in entire worbook. Can we do the coding only for 1 sheet.Please help .
  • To post as a guest, your comment is unpublished.
    Martin · 2 years ago
    Thanks u Sir its working .But in Entire workbook. If i need this Code only i particular sheet.Is it Possible ??
    • To post as a guest, your comment is unpublished.
      sa · 1 years ago
      have you found an answer?
  • To post as a guest, your comment is unpublished.
    rafhtaher · 2 years ago
    i do as above but the function of cut copy and past disabled in any excel file i open .why?
    • To post as a guest, your comment is unpublished.
      Stuart Davis · 2 months ago
      Hi, I used this to hide "Cut" from users and it works great unless you have a Table, the "Cut" miraculously re-appears when you select a cell in the Table, exactly what i'm trying to avoid. any ideas? thanks!