工作簿中每张工作表的工作表更改事件 [英] Worksheet change event for every sheet in the workbook

问题描述

有没有办法全局安装在每个单页上触发的更改事件？

我有一个52页的工作簿，我想要触发相同的事件处理程序。具有代码的中央模块将有助于管理触发器代码，但是我不想将代码添加到每个单页。

有没有办法？

解决方案

使用 Workbook_SheetChange 事件宏。 Sh 是工作表对象被传入，而目标是接收到更改的单元格或单元格。一个简单的 With ... End With statement 应该足以本地化

一个简单的时间戳记事件可能看起来像这样。

  Private Sub Workbook_SheetChange（ByVal Sh As Object，ByVal Target As Range）
 With Sh 
如果不相交（目标，.Columns（1））是Nothing然后
出现错误GoTo bm_Safe_Exit 
 Application.EnableEvents = False 
 Dim rng As Range 
对于每个rng Intersect（Target，.Columns（1））
 rng.Offset（0，4）= Now 
下一个rng 
结束如果
结束
 bm_Safe_Exit：
 Application.EnableEvents = True 
 End Sub 
  / pre> 
 
 
对任何工作表的列A中的单元格所做的任何更改都将导致当前的日期时间被放入同一工作表和行的列E中。新创建的工作表将立即受到影响。您可以解析受其工作表影响的工作表 .CodeName属性或工作表 .Name属性。除非您锁定工作簿，否则不建议使用 Worksheet.Index属性结构，使工作表无法重新排序。
 
Is there a way to globally install a change event that triggers on every single sheet? 

I have a workbook with 52 sheets and I want to have the same event handler triggered. A central module with the code would help to manage the Trigger code but I don't want to add the code to every single sheet.

Is there a way to do it? Implementing it in the workbook module didn't Trigger anything.
解决方案
Use the Workbook_SheetChange event macro. The Sh is the Worksheet Object being passed in and the Target is the cell or cells that have received the change. A simple With ... End With statement should be enough to localize teh worksheet receiving the change.

A simple timestamp event might look like this.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    With Sh
        If Not Intersect(Target, .Columns(1)) Is Nothing Then
            On Error GoTo bm_Safe_Exit
            Application.EnableEvents = False
            Dim rng As Range
            For Each rng In Intersect(Target, .Columns(1))
                rng.Offset(0, 4) = Now
            Next rng
        End If
    End With
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub
Any change made to a cell in column A of any worksheet will result in the current datetime being put into column E of the same worksheet and row. A newly created worksheet will immediately be affected. You can parse the worksheets affected by their Worksheet .CodeName property or Worksheet .Name property. The Worksheet.Index property is not recommended unless you lock the workbook structure so that the worksheets cannot be reordered.

                        
这篇关于工作簿中每张工作表的工作表更改事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！