excel - 禁用/启用复制粘贴

Question

我有这两个代码:

第一个是禁用复制粘贴的宏:

Sub Desable_Copy()

    Dim oCtrl As Office.CommandBarControl

    For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
        oCtrl.Enabled = False
    Next oCtrl

    For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
        oCtrl.Enabled = False
    Next oCtrl

    Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    With Application
        .CellDragAndDrop = False
        .CutCopyMode = False 'Clear clipboard
    End With
End Sub

第二个是启用复制粘贴的宏:

Sub Enable_Copy()

    Dim oCtrl As Office.CommandBarControl

    For Each oCtrl In Application.CommandBars.FindControls(ID:=21)
        oCtrl.Enabled = True
    Next oCtrl

    For Each oCtrl In Application.CommandBars.FindControls(ID:=19)
        oCtrl.Enabled = True
    Next oCtrl

    Application.CellDragAndDrop = True
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    With Application
        .CellDragAndDrop = True
        .CutCopyMode = True 'Clear clipboard
    End With
End Sub

当我执行代码时，我收到一条错误消息:

Ambiguous Name Detected

Answer 1

Excel 的复制/粘贴功能是为 Excel 应用程序设置的。如果您为一个工作簿禁用它们，它们将被全部禁用。如果您同时打开多个工作簿，则管理变得相当繁琐-如果您是专家级程序员，也许您不是。考虑替代方案，例如 Application.Undo可以在 Worksheet_Change 事件上运行。以下代码将撤消工作表上的任何粘贴操作。

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 18 Apr 2017

    Dim UndoList As String

    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    On Error GoTo ErrExit
    UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If Left(UndoList, 5) = "Paste" Or UndoList = "Auto Fill" Then
        MsgBox "Please don't paste values on this sheet." & vbCr & _
               "The action will be reversed.", vbInformation, _
               "Paste is not permitted"
        With Application
            .Undo
            .CutCopyMode = False
        End With
        Target.Select
    End If

ErrExit:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

此代码改编自 code published here (编辑 ML:原始链接已损坏，我发现 this one 作为替代/edit)。那里采取的观点不是为了防止粘贴操作，而是为了防止粘贴操作弄乱表格格式。这是一个非常有趣的部分，解释清楚且易于实现。