PDA

View Full Version : [SOLVED] Odd scope of Workbook_WindowDeactivate?



TheAntiGates
04-12-2017, 01:03 PM
I can put
Private Sub Workbook_WindowDeactivate(byVal Wn As Excel.Window)
Stop
End Sub in ThisWorkbook of foo.xls and it fires normally if I close a sole open workbook. (It also fires when I close the ":2" active window. That is, if I had created a new window from a workbook with Control-N or code ActiveWindow.NewWindow, and now hit Control-F4, the event fires.)

However the identical code does not fire from ThisWorkBook of personal.xls, even though other WorkBook events work fine there, e.g. Workbook_Open. I'd like the event to happen in personal.xls.

BTW to avoid false trails, note, this is WindowDeactivate event, not Deactivate event.

Also, I have not explored App_WindowDeactivate, if that's what I need to do here.

david000
04-12-2017, 07:14 PM
if I close a sole open workbook

In your personal module


Sub PERSONAL_xlsb()
Dim wb As Workbook
Dim sht As Worksheet

For Each wb In Application.Workbooks
If wb.Name <> "PERSONAL.xlsb" Then
wb.Close
End If
Next wb

End Sub


In the workbook module

Private Sub Workbook_WindowDeactivate(byVal Wn As Excel.Window)
Stop
End Sub

TheAntiGates
04-12-2017, 09:01 PM
No, I believe I was clear, I don't want the workbook's code. I already confirmed and stated that that works. Nor do I want code to do closing - I don't know where you got that idea. I don't see any point at all in your response as is.

I guess I might as well ask for someone to at least verify that you get the same "non-result" that I get with the personal routine. Did anyone try it? Just paste the code into personal's ThisWorkbook and close whatever is open - real real simple. It's similarly trivial to check an open (non PERSONAL) workbook - just paste it into its ThisWorkbook and then close it, and see if it "Stop"s.

And can someone answer if there is a meaningful basis here for which I should pursue the Application level event processing? Thanks.

david000
04-12-2017, 09:15 PM
No, I believe I was clear, I don't want the workbook's code. I already confirmed and stated that that works. Nor do I want code to do closing - I don't know where you got that idea. I don't see any point at all in your response as is.


Just trying to help, I understand you my be in a stressful position, I'm not joking around. I'm interesting in this solution, most people learn when they are wrong.

TheAntiGates
04-12-2017, 10:04 PM
Okay, David. I'll talk you through this. You don't have to reply. I'm just trying to help you learn. It's most important though that you read the question thoroughly before replying - for any post, not just mine. I'll repeat and amplify my original post so you can follow along and learn.

Close Excel completely.
Copy the 3 lines of code in my post.
Open Excel.
Go Control-N to open a new workbook. Observe the window caption. That is the name of the active workbook. Remember its name. I will call it "Book1." If your window caption shows a different name than Book1, remember what that is, and when I refer to Book1, assume that I mean your workbook's name if different than Book1.
Go alt-F11 to open a VBA window.
Go Control-R to view the Project Explorer.
Locate Book1.
Expand if not already expanded.
Double click This Workbook.
Type alt-v and C to enter the code pane.
Go control-end.
Hit the enter key
Control-V to paste the 3 lines of code.
alt-f11 to return to the active Excel window.
Control-F4 to close the active workbook Book1.
Observe if the VBA window self-activated, and if so, whether the word "Stop" is highlighted.
If the word "Stop" is highlighted, hit F5, and write down, "workbook module level success."
Else write down "workbook module level failure."

Go Control-N to open a new workbook.
Go alt-F11 to open a VBA window.
Go Control-R to view the Project Explorer.
Locate PERSONAL.
Expand if not already expanded.
Double click This Workbook.
Type alt-v and C to enter the code pane.
Go control-end.
Hit the enter key
Control-V to paste the 3 lines of code.
alt-f11 to return to the active Excel window.
Control-F4 to close the active workbook.
Observe if the VBA window self-activated, and if so, whether the word "Stop" is highlighted.
If the word "Stop" is highlighted, hit F5, and write down, "PERSONAL module level success."
Else write down "PERSONAL module level failure."

Now, what I've said, which is to test and duplicate or refute what the original post said, is something everyone should do before answering a question, including mine, unless they are highly experienced or advanced and can answer without needing to test or demonstrate the behavior.

Now, having done all that, if you know why the event did not trigger a breakpoint in the personal module, or you can otherwise explain why "Stop" did not occur in personal.xls, you can be a hero and give that answer. In this particular case, because it is an advanced question, you probably won't solve it but don't feel bad; none of the other very smart gurus have answered it either, it would appear.

Let's hope an event processing guru enlightens both of us. Or even a nonguru, who can answer the question that I asked, which is why that event is not firing from personal.xls - and whether Application level event processing is required.

I hope that clears things up for you.

david000
04-13-2017, 06:00 AM
.

rlv
04-13-2017, 06:51 AM
When I try


Private Sub Workbook_WindowDeactivate(byVal Wn As Excel.Window)
Stop
End Sub

in Excel 2010, my results are the same as yours. I suspect that Workbook_WindowDeactivate is a workbook event that is never going to fire for the windowless personal macro workbook. You might need to turn to application events.

http://www.cpearson.com/excel/AppEvent.aspx

TheAntiGates
04-13-2017, 07:06 AM
Thanks rlv, excellent tip to go to Chip for the app events. Another page that looks awfully strong for events and precedence, assuming it's accurate/reliable, is
http://pixcels.nl/events-in-workbooks/

But one thing, though - I didn't only have hidden workbooks when I attempted to have the code execute in personal.xls. I went Control-N to create an empty workbook, then closed it, but the personal.xls code did not execute. Do you or anyone also see that?

TheAntiGates
04-13-2017, 08:12 AM
AMOQ. The answer turned out to be rather straightforward. I don't know why no one answered it because in hindsight it seems obvious. Thanks rlv, because your post got me on the right track.

personal.xls ThisWorkbook workbook code (e.g. Workbook_Open, Workbook_WindowDeactivate) applies to the operation of personal.xls, not any open workbook. IOW it acts exactly like it's named - it's for "this workbook" !

So I fell into the trap of thinking that I could just take code that works in non-PERSONAL workbooks' modules, and move it over to PERSONAL's code, saving the trouble of having to enter it over and over into every workbook. That's always worked before, with the effect of taking an operation that only applies to one workbook and extending its functionality to all open workbooks; however that's not the case with This Workbook. (duh, thwapping my skull :banghead:)

The solution was to use
Sub App_WindowDeactivate
in a class module

GTO
04-13-2017, 09:36 AM
Hi there,

Sorry no one noticed your post yesterday and glad you found the oversight. Not any real help, but just in case of interest, you can of course "borrow"/house the app code in ThisWorkbook, i.e.



Option Explicit


Private WithEvents XL As Excel.Application


Private Sub Workbook_Open()
Set XL = Excel.Application
End Sub


Private Sub XL_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
MsgBox Wn.Caption
End Sub


BTW, what is AMOQ?

Mark

TheAntiGates
04-13-2017, 12:23 PM
"Answering (or answered) my own question"

I do mostly what you do except using App instead of XL
Public WithEvents App As Application
and I go
Set AppClass.App = Application

However I have the WithEvents declaration in a class module rather than ThisWorkBook. I am unable to compile and execute that Set otherwise, at least in XL03. I also have the App_ subs in the class module, because they don't run from ThisWorkbook.

BTW, in PERSONAL Module1 I go
Public AppClass As new EventClass
in order to use AppClass as such.