Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: How to use events with Application object

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm not having much luck today.

    DESIRE: Run event when someone leaves my workbook (so that I can reset the shortcut menu I used so that it does not appear in other open workbooks)

    EXPECTED CODE FROM MS EXCEL HELP THAT DID NOT WORK:
    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    MsgBox Sh.Name
    End Sub

    'This example arranges all open windows when a workbook is deactivated.

    Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
    Application.Windows.Arrange xlArrangeStyleTiled
    End Sub

    TO USE APP, MS EXCEL HELP SAID TO RUN THE CLASS MODULE InitializeApp AS PER THE FOLLOWING: I could not find any way to run this code - it would say invalid sub or function.

    I am at a loss once again...

    'MS HELP: Using Events with the Application Object
    'Before you can use events with the Application object, you must create a new class module and declare an object of type Application with events. For example, assume that a new class module is created and called EventClassModule. The new class module contains the following code.

    Public WithEvents App As Application

    'After the new object has been declared with events, it appears in the Object drop-down list box in the class module, and you can write event procedures for the new object. (When you select the new object in the Object box, the valid events for that object are listed in the Procedure drop-down list box.)

    'Before the procedures will run, however, you must connect the declared object in the class module with the Application object. You can do this with the following code from any module.

    Dim X As New EventClassModule

    Sub InitializeApp()
    Set X.App = Application
    End Sub

    'After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur.

    'See also: Application object events

  2. #2
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, I got the Window event to work, so I can do what I wanted, but I would still like to know how to get the others to work.

    All of the following were placed in "ThisWorkbook".

    WORKS:::
    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    ' Works
    MsgBox Sh.Name
    End Sub

    DOES NOT WORK:::::
    Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
    'WorkbookDeactivate Event
    ' NEVER RUNS (even though from help)
    MsgBox ("Deactivated Workbook")
    End Sub
    'This example minimizes any workbook window when it's deactivated.


    WORKS:::
    Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
    'Wn.WindowState = xlMinimized
    MsgBox ("Deactivated Window")
    End Sub

  3. #3
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-05 21:22, Brian wrote:
    Ok, I got the Window event to work, so I can do what I wanted, but I would still like to know how to get the others to work.

    All of the following were placed in "ThisWorkbook".

    WORKS:::
    Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    ' Works
    MsgBox Sh.Name
    End Sub

    DOES NOT WORK:::::
    Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
    'WorkbookDeactivate Event
    ' NEVER RUNS (even though from help)
    MsgBox ("Deactivated Workbook")
    End Sub
    'This example minimizes any workbook window when it's deactivated.


    WORKS:::
    Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window)
    'Wn.WindowState = xlMinimized
    MsgBox ("Deactivated Window")
    End Sub
    Your App events goes into the New Class module!
    That is in your Class Module and NOT the this
    workbook module....as a side note it would be
    best to set a reference to this New class @
    startup eg.

    Dim X As New EventClassModule

    Private Sub Workbook_Open()
    Set X.App = Application
    End Sub

    NB: Your newly created Class Module should be
    Named:= EventClassModule.

    So what happens is that @ startup the reference is set to point at your new class
    It is in your Class Module the the new Events
    get defined and not your std modules.



    Kind Regards,
    Ivan F Moala From the City of Sails

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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