Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Class module events not being handled (Excdel 2000/SR 1)

    Without going into the why of the requirements I have the need to have a VBA class module that also handles the ThisWorkBook objects events such as workbookopen, beforeclose, etc. The following is code froma test Excel template where a class modules named clsApp is defined as an "Application" and various events are handled. Also listed is the "ThisWorkbook" object with its code that creates an instance of the clsApp class. All of the events in the "ThisWorkbook" object gets handled as demostrated by the message boxes, but none of the same type of events get handles in the clsApp class, what am I doing wrong? Is this possible? Also since there is a need to clean memory upon exiting the Excdel app, I set the claApp object to nothing in the ThisWorkbook object's BeforeClose event but if this event is handled first then the class clsApp's BeforeClose will not be handled and this is required. Is there another event to place the Set xlApp to Nothing?

    First the "ThisWorkbook object code"
    ' Excel's ThisWorkbook object
    Option Explicit

    Private xlApp As New clsApp


    Private Sub Workbook_Activate()
    MsgBox "ThisWorkbook Activate"

    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    MsgBox "ThisWorkbook BeforeClose"
    Set xlApp = Nothing

    End Sub

    Private Sub Workbook_Deactivate()
    MsgBox "ThisWorkbook Deactivate"

    End Sub

    Private Sub Workbook_Open()
    ' Test that the class clsApp is created.
    xlApp.test

    MsgBox "ThisWorkbook Open"

    End Sub

    Now the class clsApp code
    ' claApp code
    Option Explicit
    Public WithEvents clsApp As Application



    Private Sub clsApp_WorkbookActivate(ByVal Wb As Workbook)
    MsgBox "In class WB Activate"
    End Sub

    Private Sub clsApp_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    MsgBox "In class WB BeforeClose"

    End Sub

    Private Sub clsApp_WorkbookDeactivate(ByVal Wb As Workbook)
    MsgBox "In class WB DeaActivate"

    End Sub

    Private Sub clsApp_WorkbookOpen(ByVal Wb As Workbook)
    MsgBox "In class WB Open"

    End Sub

    Public Sub test()
    MsgBox "test Sub"
    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Class module events not being handled (Excdel 2000/SR 1)

    The Thisworkbook 's Workbook_Open sub needs this extra line:

    Set xlApp.clsApp= Application
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Class module events not being handled (Excdel 2000/SR 1)

    Thanks Pieterse you always have great answers. What is you suggestion dealing with my last question of where to place the Set xlApp( my class instance) to Nothing since in my class I also have the beforeclose event and currently I am performing that step in the thisWorkbook's BeforeClose event.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Class module events not being handled (Excdel 2000/SR 1)

    The workaround for something like that would be to put the code in a normal sub and use application.ontime to call that sub :

    Normal module:

    Sub ClearRefs()
    Set .... = Nothing
    End Sub

    In the Workbook_BeforeClose of the Thisworkbook's class module:

    Application.Ontime Now,"ClearRefs"

    This ensures all event code gets executed and after that the OnTime proc gets fired.

    But I'm not sure this works as expected, it might be that XL first closes your app (after finishing workbook_beforeClose from your application class), and then reopens the workbook to execute the ClearRefs sub. (setting the ref again in it's workbook_Open event...).

    You might try setting the object to nothing in the application class module's beforeClose code (as the last line) or call the sub I already mentioned directly from it in stead of using OnTime.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •