多个外部工作簿的Worksheet_Change事件

Question

我有一个类模块，它充当外部工作簿的Worksheet_Change事件。我正在修改我的项目，而当前的设置只允许一个外部工作簿sheet_change事件。但是，我希望此事件侦听多个工作簿上的更改。

工作簿的数量在代码中途是未知的，因此我不能为每个工作簿创建预定数量的类。

模块Functions

Dim oWb2 As New UpdaterUnkowns

Public Function
'Code...
Set oWb2.Workbook = newfile
End Function

类模块UpdaterUnknowns

Public WithEvents m_wb As Workbook
Public CellVal As String

Public Property Set Workbook(wb As Workbook)
    Set m_wb = wb
End Property

Public Property Get Workbook() As Workbook
    Set Workbook = m_wb
End Property

Public Sub m_wb_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'Code...

End Sub

Set oWb2.Workbook = newfile为类模块设置工作簿。我是否可以将多个工作簿传递给事件？

Answer 1

要处理每个打开的工作簿，您可以这样做：

Dim UU_collection As Collection
sub mysub()
   Set uu_collection = New Collection
   Dim wb As Workbook
   For Each wb In Application.Workbooks
      Dim oWb2 As UpdaterUnknowns
      Set oWb2 = New UpdaterUnknowns
      Set oWb2.Workbook = wb
      uu_collection.Add oWb2
   Next wb
End Sub