如何改进我的短VBA代码如下？

I tried to protect cells on my workbook from being edited. I wrote this code,

我试图保护我的工作簿上的单元格不被编辑。我写了这段代码,

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
End Sub

But after some tests, I caught an exception. If I cut a cell and paste to another cell, it's ALLOWED! I'm not sure if there are other exceptions like this that I haven't figured out. My question is how do I protect cells being edited but meanwhile able to be copied?

但经过一些测试,我发现了一个例外。如果我切割一个单元格并粘贴到另一个单元格,它是允许的!我不确定是否还有其他类似的例外我还没弄明白。我的问题是如何保护正在编辑的单元格,同时又能够复制?

2 个解决方案

#1

Use the interface only option, this allows the sheet to be locked - but only for user interactions. Any code can interact with the sheet without being blocked:

使用仅界面选项,这允许锁定工作表 - 但仅限于用户交互。任何代码都可以与工作表交互而不会被阻止:

Private Sub Workbook_Open()
    For Each ws In ThisWorkbook.Sheets
        ws.Protect UserInterfaceOnly:=True
    Next
End Sub

#2

without protecting the sheets the only solution I can think of is:

没有保护床单,我能想到的唯一解决方案是:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
  If Application.CutCopyMode = 2 Then Application.CutCopyMode = 0
End Sub

Should be self-explaining ;)

应该是自我解释;)

or runn all sheets like:

或运行所有表格如:

Sub protectAllSheets()
  Dim x As Variant
  For Each x In ThisWorkbook.Sheets
    x.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
  Next
End Sub

#1