Excel VBA:在剪切/粘贴/删除/插入事件上触发宏

Question

我有一个定义为宏的条件格式规则，它删除旧规则并用更新规则替换它们：

Sub setCondFormat()
Set Table = ActiveSheet.ListObjects("Rules")
Table.Range.FormatConditions.Delete
Set Attribute = Table.ListColumns("Attribute")
With Attribute.DataBodyRange.FormatConditions _
.Add(xlExpression, xlEqual, "=ISEMPTY(A2)")
With .Interior
.ColorIndex = 0
End With
End With
End Sub

Excel 中的条件格式需要更新。否则 规则中的单元格区域变得碎片化。

假设您有两个规则：

1. 使$A$1:$A$30变红
2. 将$B$1:$B$30变为蓝色 现在选择A10:B10并将其复制/粘贴到A20:B20.
   Excel将要做的是删除条件格式。

对于应用于这些单元格的规则A20:B20并添加新的 具有A20:B20格式的规则。你最终得到四个 规则：

1. 使=$A$20变红
2. 使=$B$20蓝色
3. 使=$A$1:$A$19,$A$21:$A$30变红
4. 使=$B$1:$B$19,$B$21:$B$30蓝色

当表结构通过剪切/粘贴/删除/插入事件更改时，就会发生这种情况。

如何在剪切/粘贴/删除/插入事件上触发上述VBA宏？

Answer 1

您可以使用宏的快捷方式

复制时触发VBA事件？

如果你不想这样做，你需要使用Windows API：

编辑单元格时按下键时是否有任何事件会触发？

Answer 2

我找到的解决方案是在打开工作簿时使用表格的内容创建一个新工作表。首先，您需要创建一个包含公共变量的模块。

Public OldRange As Range
Public NewRange As Range
Public Table As ListObject

然后，使用事件打开您的工作簿。

Private Sub Workbook_Open()
Dim sh As Worksheet
Dim address As String
For Each sh In Worksheets
If sh.Name = "DATA" Then
Worksheets("DATA").Activate
ActiveSheet.Delete
End If
Next
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "DATA"

Set sh = ActiveWorkbook.Sheets("Plan1")
sh.Activate
Set Table = ActiveSheet.ListObjects("Rules")
Set OldRange = Table.Range
address = Table.Range.address
Table.Range.Copy
Set sh = ActiveWorkbook.Sheets("DATA")
sh.Activate
Range(address).PasteSpecial (xlPasteAll)
End Sub

然后，使用事件Worksheet_Change使用之前保存的表验证原始表的内容。

Private Sub Worksheet_Change(ByVal Target As Range)
Set Table = ActiveSheet.ListObjects("Rules")
If Intersect(Target, Table.Range) Is Nothing Then Exit Sub 'this will guarantee that the change made in your sheet is in your desired table
Set NewRange = Table.Range
Dim rng As Range
Dim rngaddr As String
Dim TableChanged As Boolean
TableChanged = False
For Each rng In NewRange
rngaddr = rng.address
If rng.Value <> ActiveWorkbook.Sheets("DATA").Range(rngaddr).Value Then
'do something
TableChanged = True
End If
Next
End Sub

请记住：每次更改表格时，您都需要保存表格的内容。