CODEWITHSUNDEEP
vbaexcelevents
paul590
paul590

Reputation: 1425

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

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

Thank you for your help!

Upvotes: 0

Views: 751

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Here's a simple example:

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

In a regular module:

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

Upvotes: 4

Related Questions