Workbook Event Macros Triggering in Other Workbooks

[Mrock1]

Hi

I make fairly extensive use of workbook event macros such as Workbook_Open, _Activate, _SheetCalculate, _SheetChange, _SheetSelectionChange and Worksheet event macros such as Worksheet_Activate, _Change and _FollowHyperlink.

These work great except occasionally and very intermittently and most notably, Workbook_open in one of my workbooks will suddenly trigger when another workbook is opened in the same Excel instance. The problem is that my Workbook_Open macro assumes it is the active workbook, even when the workbook and worksheet.range references are fully quaified, and looks for certain values, tables, etc, which when not found produces a Debug error. I can correct this on the fly simply by making the correct workbook active and continuing the macro execution and all is well (fortunately Workbook_Open only sets up some default public variables so does not interfere with its normal operation). I can't reproduce this as it only happens every now and then. This particular workbook does include an Ontime event to run a macro every 15 minutes. My only theory is that somehow the Ontime trigger may occur at the exact time another workbook is opened which somehow precipitates the error, but even that doesn't make too much sense and it is impossible to reproduce. If I do experience this, I can correct it on the fly by simply making the right workbook the active workbook and continuing the macro execution.

My only workaround is to ensure that this workbook is open in a completely independent Excel instance by making use of the Alt-key hold when opening another workbook, a workaround I've been using successfully for almost 3-years. However, I also allow other less savvy Windows and Excel personnel use the same workbook and since it's quite difficult to explain how to use the Alt-key to start a separate instance, what to expect and what this even means, this process often falls on deaf ears. So, they tend to load up all their workbooks in a single session and the error manifests intermittently as I've explained. These people typically also don't understand the way to make the right workbook active on the desktop and continue the macro execution. FYI: All my reference to the correct workbook and any worksheet, cell, range or table within that workbook are full qualified (e.g. Workbook(file path+name).Worksheets(sheet name).range(range name), in an attempt to avoid ambiguities such as activeworkbook, activesheet, or other such default references to cell, ranges and tables. Nevertheless, the problem continues to manifest intermittently. Even my references to cells and ranges in the Workbook_Open macro!

Any ideas would be valuable.

Thanks
Max

 

[RoryA]

I think we’d need to see actual code. Workbook_open should never be able to be triggered by another workbook opening. Also, the syntax workbooks(file path + name) is wrong as you do not include the path.

 

[Mrock1]

I think we’d need to see actual code. Workbook_open should never be able to be triggered by another workbook opening. Also, the syntax workbooks(file path + name) is wrong as you do not include the path.

Couple of things. 1 - I agree that workbook-open should never be able to be triggered by another workbook opening, but that’s just my point. Evidence tells me that sometimes very intermittently this is exactly what is happening. On occasion the while opening another workbook the event macro in my workbook actually triggers for no good reason. I know this because the debug error window appears and choosing debug drops me into the workbook_open macro, or at least the macro it subsequently calls to do all the heavy lifting which I call Workbook_open2. So while it should never happen, it obvious does in some strange alignment of the planets. As for seeing the code, that’s difficult because there’s a lot and it’s proprietary information.

As for the inclusion of that path in the workbook function: my bad - you are quite correct and I don’t. However all my references to cells, ranges and tables are fully qualified in an attempt to avoid ambiguity- workbook.worksheets.range. They all work normally and have for years. I get Thai problem manifest rarely and can’t figure out the circumstances in which it occurs. So I can’t reproduce it for debug purposes. All I know is if I bring the workbook to the front in Windows I can continue the macro with no further error.

 

[RoryA]

or at least the macro it subsequently calls to do all the heavy lifting which I call Workbook_open2

I suspect that is key information. How does it call it? Also, what is the actual error message?

 

[Anthony P Rose]

I find this interesting. I have a large and complex workbook with few events but it does have Workbook_Activate. This workbook remains open for most of my day on a worksheet which contains cells using a custom function. This custom function does not exist in any other workbooks I have.
What happens is that when I open or create a new workbook with macros, but no events, while this 2nd workbook is active and I am running a macro in it in debug mode pressing nothing but F8, suddenly the logic thread will jump into the custom function.
It seems as though the passive complex workbook somehow makes itself active for no good reason.
I think this confirms what OP is experiencing.
OP: what about forcing a new Excel instance for your own workbook? i.e. on opening, check if there are other workbooks in the instance, and if so, start a new instance and re-open yourself; and on activation, if there is now another workbook in the instance, make it active... I realise this line of thought needs to be expanded upon and may not work, but it might be worth exploring. I could give you example code if you like.
.
For what it's worth, ever since 365, my complex workbook also takes it into its head to close in an instant - without saving or messaging - at random times, commonly when inserting a row (but there is no Workbook_change event).
Overall I have an increasing suspicion that Microsoft are getting into a tangle pushing Office into the web.

 

[Mrock1]

I find this interesting. I have a large and complex workbook with few events but it does have Workbook_Activate. This workbook remains open for most of my day on a worksheet which contains cells using a custom function. This custom function does not exist in any other workbooks I have.
What happens is that when I open or create a new workbook with macros, but no events, while this 2nd workbook is active and I am running a macro in it in debug mode pressing nothing but F8, suddenly the logic thread will jump into the custom function.
It seems as though the passive complex workbook somehow makes itself active for no good reason.
I think this confirms what OP is experiencing.
OP: what about forcing a new Excel instance for your own workbook? i.e. on opening, check if there are other workbooks in the instance, and if so, start a new instance and re-open yourself; and on activation, if there is now another workbook in the instance, make it active... I realise this line of thought needs to be expanded upon and may not work, but it might be worth exploring. I could give you example code if you like.
.
For what it's worth, ever since 365, my complex workbook also takes it into its head to close in an instant - without saving or messaging - at random times, commonly when inserting a row (but there is no Workbook_change event).
Overall I have an increasing suspicion that Microsoft are getting into a tangle pushing Office into the web.

Hi Anthony. I like your notion that custom functions may be triggering this symptom in some way as I do use them. Most notable I use a custom split function to split a delimited text string into an array (the reported textsplit function seems to not be available in my instance of O365, but my application predates that in any case). That said such a custom function should still be contained within a single workbook and I have been unable to manually recreate the error condition. All that I do know is that when my custom workbook events trigger in the wrong workbook and I enter the debug mode, clearly I’m in the wrong workbook and cell values and ranges expected by the macro are not available despite the macro explicitly naming the workbook, worksheet and range. So by my interpretation, it shouldn’t matter what workbook was active.

As for the sudden exit by excel only seems to occur if I have the workbook stored on SharePoint/Teams (in my case) whether or not there are multiple people with the workbook open. I find that clearin lg the cache through Excel > File > Open > Files needing attention and deleting the local copies of listed files, does fix the problem temporarily although the offending workbook is sometimes not listed. And, yes, empirically, I tend to agree that MS has introduced problems in pushing Office to the cloud from a traditional far client. That said, when it does work, it works. It’s just these unexpected errors (let’s not mention the upload failed in which changes are lost).

It heartening to finally learn that other people have the same issues. Thanks for your input.

 

[Anthony P Rose]

Right - my sudden exit issue workbook is also on Sharepoint. Good to know, thank you.

 

[Anthony P Rose]

One more thought. In both our cases, the macro being started is in the inactive workbook. So another cure (perhaps better than trying to get users to keep the workbook in its own instance of Excel) might be on entry of macro to check if the workbook is inactive (not the same as parent of active sheet) or the cell references are invalid, and if so, simply quit, rather than making the workbook active and trying to complete the macro. I think the error is in the macro being triggered rather than in the workbook not being active.

 

[Anthony P Rose]

Update: I have discovered the cause of my issue (which is likely different from yours, unless in your code you activate your workbook).
It is the fact that I have a function declared as "Function Word1(... ".
This is only referenced in cells in the workbook in which it is defined, call it Big workbook.
When I do certain things in another workbook which does not mention Word1, call it Small workbook, such as Copy and Paste, after the paste completes the function in Big gets called for multiple cells unrelated to the copy/paste range. There is no sheet change event coded for the workbook or the sheet.
The Big workbook does not activate and does not run the Workbook_SheetChange event.
Breakpointing inside the function, upon Ctrl-L, the only item in the stack is the function itself.
But if I change the name of the function to W1, the copy and paste completes fine without calling the function.

 

[Anthony P Rose]

Final update: It is not the function name. It is the fact that when you update a cell in a 2nd workbook and it triggers an Excel recalc, cells in the 1st workbook whose value is dependent on a function will call that function in the recalc.
My solution is to add VB code to the workbook using functions, as follows:

In ThisWorkbook:

Private Sub Workbook_Activate()
If ThisWorkbook.ActiveSheet.Name <> "function-using worksheet's name" Then ' Avoid calling of Word1 function due to 2nd workbook updates forcing recalc
ThisWorkbook.Worksheets("function-using worksheet's name").EnableCalculation = False
End If
End Sub

In the Worksheet's code:

Private Sub Worksheet_Activate()
With Me
.EnableCalculation = True
End With
End Sub

Private Sub Worksheet_Deactivate()
With Me
.EnableCalculation = False
End With
End Sub