Excel VBA 阻止删除单元格但允许编辑
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10445742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA prevent deletion of cells but allow edit
提问by AdRock
I have made a spreadsheet which a user can enter a postcode and a quantity into 2 cells and I have other cells doing calculations and displaying the results.
我制作了一个电子表格，用户可以在其中输入邮政编码和数量到 2 个单元格中，并且我让其他单元格进行计算并显示结果。
I have added some VBA to prevent anyone from deleting rows and columns but I would like to prevent deletion of any cell within a range but also allow a user to make changes to certain cells but also prevent editing of cells with formula in there.
我添加了一些 VBA 来防止任何人删除行和列，但我想防止删除某个范围内的任何单元格，但也允许用户对某些单元格进行更改，但也防止编辑带有公式的单元格。
In cell E4, the user can enter a postcode. In E6, the user can enter a quantity. These can be edited but not deleted. E8:E9and E11:E14are all drop down lists (validation) which hold data from lists. These can be changed using the drop down but not deleted.
在 cell 中E4，用户可以输入邮政编码。在 中E6，用户可以输入数量。这些可以编辑但不能删除。E8:E9并且E11:E14都是包含列表中数据的下拉列表（验证）。这些可以使用下拉菜单进行更改，但不能删除。
L10:L14, L16, L23:L27, L29, L30:L33can all have their data edited but not deleted.
L10:L14, L16, L23:L27, L29,L30:L33都可以编辑但不能删除其数据。
What would the VBA for this look like? I guess it would use the Worksheet_Change() event.
这个 VBA 会是什么样子？我猜它会使用Worksheet_Change() event.
回答by Siddharth Rout
Is this what you are trying? Users can edit cell E4and E6but they cannot leave it empty. I am also assuming that the cell are not empty before hand.
这是你正在尝试的吗？用户可以编辑单元格E4，E6但不能将其留空。我还假设该单元格事先不是空的。
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoa
Application.EnableEvents = False
If Not Intersect(Target, Range("E4")) Is Nothing Then
If Len(Trim(Range("E4").Value)) = 0 Then Application.Undo
ElseIf Not Intersect(Target, Range("E6")) Is Nothing Then
If Len(Trim(Range("E6").Value)) = 0 Then Application.Undo
End If
LetsContinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
FOLLOWUP
跟进
Thanks that is what i want to do. What about the other ranges? Is it just a case of loads of IF THEN or can we use a CASE and loop through? – AdRock 2 mins ago
谢谢，这就是我想做的。其他范围呢？这只是大量 IF THEN 的情况还是我们可以使用 CASE 并循环遍历？– AdRock 2 分钟前
Add/Delete cell addresses from below as applicable.
根据需要从下方添加/删除单元格地址。
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoa
Application.EnableEvents = False
If Not Intersect(Target, Range("E4,E6,E8:E9,E11:E14,L10:L14,L16,L23:L27,L29,L30:L33")) Is Nothing Then
If Len(Trim(Target.Value)) = 0 Then Application.Undo
End If
LetsContinue:
Application.EnableEvents = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue
End Sub
回答by mshthn
You are partly right but Worksheet_Change() is triggered after the change, so after the deletion. What I'd do is to have a hidden sheet to store the values entered by the user and then you can check in Worksheet_Change() whether the new value is empty (deleted) or not.
您部分正确，但 Worksheet_Change() 在更改后触发，因此在删除后触发。我要做的是有一个隐藏的工作表来存储用户输入的值，然后您可以在 Worksheet_Change() 中检查新值是否为空（已删除）。
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E" Then
' check the previous value on the hidden sheet here, if changed, then save it, if empty, then restore it
End If
End Sub
