从模块中为另一个WB添加一个事件 [英] Addign an Event for another WB from a module

问题描述

这个问题可能已经回答了，但我似乎找不到我正在寻找的答案。我正在创建一个模块，当被调用时，它创建一个新的工作簿，并将信息从这个工作簿转移到新的工作簿。我想使用这个宏添加一个新的工作簿，但没有运气。目前我有以下内容：

This question might have been answered already but I cant seem to find the answer I am looking for. I am creating a module which when called it creates a new workbook and transfers information from this workbook to the new one. I would like to add an event to that new work book using this macro but had no luck. Currently I have the following:

Public Sub TemplateCreate()
Dim NewBook as Workbook
set NewBook = addnew
End Sub

Function Addnew() as Object
Application.SheetsInNewWorkbook = 2
Application.EnableEvents = True
Set AddNew = Workbooks.Add
    With AddNew
        .SaveAs Filename:="test.xls"
    End With
End function

上面的代码非常好，但是当添加了暗淡的事件Ne​​wbook作为工作簿时，我收到一个错误：仅在对象模块上有效。有没有类似的代码行使其适用于模块？

The Above code works great, but when Adding dim withEvents Newbook as workbook I receive an error: Only valid on Object module. Is there a similar line of code to make it work for a module?

我尝试添加以下事件函数，没有运气使其工作：

I tried adding the following event function with no luck of making it work:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Tried changing between target and thisWB
If thisWB.Sheets("sheet1").Cells.Count = 1 And IsEmpty(thisWB.Sheets("sheet1")) Then thisWB.Sheets("sheet1").Interior.Color = vbBlue
End Sub

感谢您的帮助！

推荐答案

这是一个简单的例子：

在名为clsWB的类模块中：

In Class module named "clsWB":

Option Explicit

Private WithEvents m_wb As Workbook

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

'EDIT: added Getter for workbook
Public Property Get Workbook() As Workbook
    Set Workbook = m_wb
End Property

Private Sub m_wb_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "You selected " & Target.Address() & " on '" & Sh.Name & "'"
    '... or however you want to respond to the trapped event
End Sub

'EDIT2: trap sheet changes
Private Sub m_wb_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    MsgBox "You changed " & Target.Address() & _
           " to " & Target.Value & _
           " on '" & Sh.Name & "'"
    '... or however you want to respond to the trapped event
End Sub

在常规模块中：

Option Explicit

Dim oWb As New clsWB

Sub Tester()
    Dim AddNew As Workbook, ns As Long

    ns = Application.SheetsInNewWorkbook 'save default
    Application.SheetsInNewWorkbook = 2
    Set AddNew = Workbooks.Add()
    Application.SheetsInNewWorkbook = ns 'restore previous default

    AddNew.SaveAs Filename:="test.xls"

    Application.EnableEvents = True 'make sure events are enabled
    Set oWb.Workbook = AddNew

    'EDIT: set value in workbook
    oWb.Workbook.Sheets("sheet2").Cells(x,y).Value = "Test"

End Sub

这篇关于从模块中为另一个WB添加一个事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！