我可以保护我的工作表，但我想允许用户在 editinRange 中手动粘贴来自另一个Excel的值，但我不希望该用户也使用粘贴进行格式化 .

这是我目前的代码 .

Private ws As Worksheet 


Sub protectSheet()

Dim editingRange As Range
Set editingRange = ws.Range(ws.Cells(4, 2), ws.Cells(22, 10))

    ws.Protect

    With editingRange
        .EnableOutlining = True
        .Protect UserInterfaceOnly:=True
        .Protect Password:="", _
        DrawingObjects:=True, Contents:=True
    End With

End Sub

我的编码挑战说明了 .

enter image description here

我去看下面的建议链接，并从那里部分工作的代码复制 . 现在，当我尝试复制一些excel文件数据时，代码会生成错误通知 . 我希望代码不关心格式化等 . 它只是将值复制并粘贴到我的工具中 .

Range类的PasteSpecial方法失败

enter image description here

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim UndoString As String
    Dim srce As Range
    Dim trgt As Range

    UndoString = Application.CommandBars("Standard").Controls("&Undo").List(1)
    If left(UndoString, 5) = "Paste" Then 'Only allow Paste Special|Values

            Application.ScreenUpdating = False
            Application.EnableEvents = False

            Application.Undo
            Target.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False

            Application.ScreenUpdating = True
            Application.EnableEvents = True

    ElseIf UndoString = "Auto Fill" Then

        Application.ScreenUpdating = False
        Application.EnableEvents = False

        Set trgt = Selection

        Application.Undo

        Set srce = Selection

        srce.Copy

        trgt.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

        Application.ScreenUpdating = True
        Application.EnableEvents = True

    End If


Application.CutCopyMode = True
  Exit Sub

err_handler:
    MsgBox "Something went wrong when pasting the values."
    Application.EnableEvents = True
End Sub
excel vba excel-vba copy-paste