Consulting

Results 1 to 3 of 3

Thread: When you have one workbook, two windows, does any event happen closing second window?

  1. #1
    VBAX Regular
    Joined
    Aug 2019
    Posts
    9
    Location

    When you have one workbook, two windows, does any event happen closing second window?

    This is about Workbook events and possibly Window events. Also, for versions with MDI prior to the Microsoft's deeply insane SDI change.
    Start excel fresh. No visible workbooks.
    Control-N
    Alt-W, N

    Is there an event from now going Control-W or Control-F4? It's not a sheet event. And it's not a workbook close - just one window of a workbook. I suppose I'm longing for Window_close or Window_BeforeClose or Window_DeActivate or such.

    Note: I have tried the following code in personal.xls ThisWorkbook and a Class Module and no Stop happens. It seems to only be interested in those events with personal.xls itself. The only code I've seen run of the following is the Workbook_Open.

    To test the following code I did the following but with total non-success.
    Control-N
    Alt-W, N
    Alt-W, N
    Control-W (closing window 2)
    Control-W (closing the workbook)
    I don't seem to be able to get any of the following events to happen for Book1.xls - they only seem to work for personal.xls. As you might guess, I don't want to write code for every workbook's ThisWorkbook and such. I want to write it once in personal.xls, and have it apply to every workbook opened.

    'personal.xls's Module1:
    Public AppClass As New EventClass ' needed to use App_ subs   'Note, removing New is contemplated
    
    'personal.xls ThisWorkbook:
    Private Sub Workbook_Open() 'Is this the first thing to run when opening Excel? (assuming obviously that you have a \startup\personal.xls)
      'Set AppClass = New EventClass 'new line contemplated, along with removing New from Public declaration
      Set AppClass.App = Application  ' needed to use App_ subs
    End Sub
    
    'personal.xls EventClass:
    Public WithEvents App As Application
    
    Private Sub App_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
        Stop
    End Sub
    Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
        Stop
    End Sub
    Private Sub App_WorkbookDeactivate(ByVal Wb As Workbook)
        Stop
    End Sub
    Additional confusion: I have never observed the following code working in Personal's ThisWorkbook EXCEPT when I unhid personal, and closed just that personal workbook. In other words, when I close Excel, the msgbox doesn't appear. What is the sequence of events on closing Excel? Is that event skipped, or does it just refuse to interrupt its departure with a msgbox? So a Stop or msgbox are just not processed before Excel is gone?

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
      MsgBox "Wow, this Sub seems to finally have run"
    End Sub
    Final note: from time to time I have to re-issue
    Set AppClass.App = Application
    because things like App_WorkbookBeforeSave stop working. MAYBE it's due to interrupting code without letting it complete, but it seems to happen more often than only to blame that.

  2. #2
    VBAX Regular
    Joined
    Aug 2019
    Posts
    9
    Location
    Jeez, just now App_Window_Deactivate DID work for my test, as does App_WorkbookBeforeClose and App_WorkbookDeActivate, each when appropriate. I had testing them 50 times before posting and they never worked. This time, I made a point of issuing the command
    Set AppClass.App = Application
    this last time before doing the test, so that's the only feeble theory that I have as to why the App events seem to work now. I think that every time I edited the EventClass class module I destroyed AppClass.App.

    EDIT: I'm making solved but further comment, analysis and education regarding other elements of my post are still appreciated, namely, why I keep having to restore AppClass.App. I'm making a separate question anyway.
    Last edited by LarryG; 11-09-2019 at 10:04 PM.

  3. #3
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Larry, in the VBE, press F2, then select Excel in the "All Libraries" Drop Down. Then find and click on Application. Scroll the right hand pane
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

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