☰

阻止用户粘贴，但允许粘贴值

tag-icon • tag-icon microsoft-excel worksheet-function vba
阻止用户粘贴，但允许粘贴值

我的工作簿中有下面的代码，它可以阻止用户粘贴任何内容。

我的需求现在已经改变，我希望能够让用户仅粘贴值。

有办法实现这个吗？

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
Private Sub Workbook_Open()
    MsgBox "Copying and Pasting into the skills matrix has been disabled to prevent changes in the workbook functionality."
End Sub

答案1

此 VBA 代码应执行以下操作：

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    On Error Resume Next
    Target.PasteSpecial xlPasteValues
    Application.CutCopyMode = True
End Sub

答案2

根据我之前的回答，这可行吗？

因此，我们检查是否粘贴了某些内容，然后保存该值，撤消粘贴并重新插入该值。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim UndoList As String, StoreValue As String
UndoList = Application.CommandBars("Standard").Controls("&Undo").List(1)
If Left(UndoList, 5) = "Paste" Then
    StoreValue = Target.Value
    With Application
        .EnableEvents = False
        .Undo
    End With
    Target.Value = StoreValue
End If
Application.EnableEvents = True
End Sub

如果评论中的建议不是更好的解决方案。

答案3

我成功尝试的另一种方法可能是这样的：

劫持 CTRL+C 和 CTRL+V，使其仅粘贴特殊值。

因此，通过使用原始工作簿代码，但替换 

Application.OnKey "^c", ""

和

Application.OnKey "^{v}", "pasteValues"
Application.OnKey "^{c}", "copyValues"

然后创建一个具有以下内容的模块：

Public copyVal As String
Private Sub pasteValues()
selection = copyVal
End Sub
Private Sub copyValues()
copyVal = selection
End Sub

现在您可以复制和粘贴，但它只会移动值。

相关内容