LarryG
11-09-2019, 07:49 PM
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.
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.