Macros in Two Workbooks Interfere with Each Other

When Bruce has two workbooks open, the macros from the workbooks interfere with each other. Bruce stepped through one subroutine in the active workbook only to see it jump to a macro in the other workbook. There was no call procedure to do this; it just jumped. Bruce wonders what would cause this strange behavior.

This is a hard one to troubleshoot remotely; it is something that can be figured out much easier when actually working with the two workbooks.

There are some things, however, that can be suggested as starting points. First, you'll want to check to make sure that there are no name conflicts between the macros in the two workbooks. Identical procedure names can, under some circumstances, cause confusion in the execution of VBA code.

Second, take a look at any variables that may be defined at a global scope. If the same variable names are defined as "global" in both workbooks, it can give undesired results.

The most likely scenario, however, is that one of the workbooks has one or more event handlers in play, and they are getting triggered. For instance, let's say that Workbook1 has a regular macro in it, and that Workbook2 has an event handler that is triggered whenever something changes in that workbook. If your macro in Workbook1 is using a cell reference that results in a change in Workbook2, then that will trigger the event handler and, all of a sudden, you'll be running code in Workbook2 (and understandably so).

The solution is to make sure that your macro in Workbook1 is very specific in how it references cells to make sure it is only referencing cells in Workbook1. That will prevent the event handler in Workbook2 from taking over.

Closely related to this is that Workbook2 may have some macros that are triggered automatically after a given time. For instance, a macro could be developed that runs every five minutes. If that is happening in Workbook2, then even if you are running a macro in Workbook1, when the appointed time arrives, the macro in Workbook2 takes over until it is done running.

It is going to take some sleuthing to see what, exactly, is happening. Bruce is starting out correctly, single-stepping through the subroutine in Workbook1. If the jump to code in Workbook2 always happens at exactly the same step in Workbook1, then check to see if the previous line in Workbook1's code made any change in Workbook2 (intentional or inadvertent). Then examine the modules in Workbook2 to see if it is actually some event handler that has been triggered. If so (and you don't want it triggered), then your code in Workbook1 will need to turn off events before the change and then turn on events afterward.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12844) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.