Excel - 如何将代码更改为工作簿而不是工作表

Question

我有以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Set KeyCells = Range("C9")
    Set isect = Application.Intersect(KeyCells, Range(Target.Address))
    If Not isect Is Nothing Then
        Application.EnableEvents = False
        isect.Value = isect.Value - 40
        Application.EnableEvents = True
    End If
End Sub

我想要做的是让它通用，即适用于所有工作表而不只是一张。怎么做？

Answer 1

将代码移至ThisWorkbook页面

并将事件更改为事件Workbook_SheetChange。

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Set KeyCells = Range("C9")
    Set isect = Application.Intersect(KeyCells, Range(Target.Address))
    If Not isect Is Nothing Then
        Application.EnableEvents = False
        isect.Value = isect.Value - 40
        Application.EnableEvents = True
    End If
End Sub