vba 如何在excel中记录删除事件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3656144/
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
How to record delete event in excel
提问by gizgok
I have a situation where I need to know if manually a delete was performed on any row in my spreadsheet.How can I record this event in vba.
我有一种情况，我需要知道是否对电子表格中的任何行手动执行了删除。如何在 vba 中记录此事件。
回答by Ryan
Private Sub Worksheet_Change(ByVal Target As Range)
'UsedRange works fine provided there is always something in Row1
'Declare varY from a Module and set from Workbook_Open or Sheet.Activate,
'or undo first sheet change, but set variable
If Target.Columns.Count = 16384 Then
Select Case Sheet1.UsedRange.Rows.Count
Case Is > Sheet1.Range("" & varY & "").Rows.Count
MsgBox "insert row"
Sheet2.Rows(Target.Rows.Address).Insert Shift:=xlDown, _
CopyOrigin:=xlFormatFromLeftOrAbove
Case Is < Sheet1.Range("" & varY & "").Rows.Count
MsgBox "delete row"
Sheet2.Rows(Target.Rows.Address).Delete Shift:=xlUp
End Select
Else: Sheet2.Range(Target.Address).Value = Target.Value
End If
varY = Sheet1.UsedRange.Address
End Sub
回答by Dick Kusleika
Excel doesn't provide a good way to determine if a row or column has been deleted. One way you can do it is using a defined name and the Calculate event. If you care about the first 1,000 rows, for example, name cell A1001 as "rngLastRow". Then use this code in the sheet's calculate event
Excel 没有提供确定行或列是否已被删除的好方法。一种方法是使用定义的名称和计算事件。例如，如果您关心前 1,000 行，请将单元格 A1001 命名为“rngLastRow”。然后在工作表的计算事件中使用此代码
Private Sub Worksheet_Calculate()
If Me.Range("rngLastRow").Row < 1001 Then
MsgBox "Row deleted"
End If
End Sub
It won't tell you which row was deleted, so you'll need something different if you need to know that.
它不会告诉您哪一行被删除，因此如果您需要知道这一点，您将需要一些不同的东西。
If you want to simply prevent people from deleting rows, you could use an array formula in a hidden column. Again, assuming your app uses the first 1,000 rows, select A1:A1000 (or some other empty column) and type =1 and commit with Control+Enter. That will create an array in A1:A1000. When the user tries to delete a row, they will get the unhelpful message "You cannot change part of an array.", but they won't be able to delete the row.
如果您只想阻止人们删除行，您可以在隐藏列中使用数组公式。同样，假设您的应用程序使用前 1,000 行，选择 A1:A1000（或其他一些空列）并键入 =1 并使用 Control+Enter 提交。这将在 A1:A1000 中创建一个数组。当用户尝试删除一行时，他们会收到无用的消息“您不能更改数组的一部分。”，但他们将无法删除该行。
回答by Simon Cowen
You could always do something like:
你总是可以做这样的事情：
Public lngPreviousRow As Long
Public lngCurrentRow As Long
Private Sub Worksheet_Calculate()
lngCurrentRow = Me.Range("rngLastRow").Row
If lngCurrentRow < lngPreviousRow Then
MsgBox "Row deleted"
End If
lngPreviousRow = lngCurrentRow
End Sub
So long as you set up lngPreviousRow to the intially (Workbook_Open e.g) it will always detect a row deletion, regardless of any row inserts etc.
只要您将 lngPreviousRow 设置为初始（例如 Workbook_Open），它就会始终检测到行删除，而不管是否有任何行插入等。
