I'm experiencing a problem where any VBA code that I have placed in a Workbook_Open procedure in the ThisWorkbook module in any Excel workbook fails to consistently run when the workbook is opened. Weirdly, if I add a Workbook_BeforeClose procedure alongside the Workbook_Open procedure, the VBA code placed in the former does run reliably each time that a workbook is closed, even though the VBA code placed in the latter often fails to.

Every workbook on which I've experienced this problem is stored on OneDrive and synced to five different computers. What's confusing me is that the problem appears to happen only on one of the five computers, with the other four computers appearing to be unaffected somehow; VBA code in a Workbook_Open procedure automatically runs without issue on four of our five computers just as it always has on all five, but recently, it's gotten in the habit of failing to automatically run on the fifth computer.

I've never experienced any problem with VBA code in a Workbook_Open procedure successfully running on any of the five computers, until recently when it stopped running on the fifth computer in particular. Every once in a blue moon, the VBA code may run the first time that a workbook is opened only (possibly even the second time that it's opened if I'm lucky), after which it fails to run with each subsequent opening of the workbook. While using the fifth computer, I can manually run the VBA code while in the Visual Basic Editor and it will run without issue, but for whatever reason, it refuses to run when a workbook is opened.

Although the problem occurs with any Excel workbook that is opened on the fifth computer, it's proving to be especially irritating with two of our workbooks in particular; for the purpose of discussing the problem, I'll refer to them as Workbook A and Workbook B. Workbook A is currently being actively accessed by users on all five computers. Workbook B is currently being accessed by just me on all five computers, but mostly on the fifth computer as I'm currently in the process of developing and debugging Workbook B.

As far as I can tell, everything is set up correctly to allow both the VBA code to run and the two workbooks to sync via OneDrive without issue (the following setup is shared between Workbook A and Workbook B, as well as among all five computers):

• The workbook is saved as a Microsoft Excel Macro-Enabled Worksheet with the .xlsm file extension.
• The macro security settings in the Trust Center are set up as follows:
  • In the Trusted Documents section, I have the "Allow documents on a network to be trusted" checkbox checked.
  • In the Macro Settings section, I have the "Disable VBA macros except digitally signed macros" radio button selected; accordingly, in the Trusted Locations section, I have the "Allow Trusted Locations on my network" checkbox checked, and I have the OneDrive file path to the folder that contains the workbook saved in the list of trusted locations (I've tested and implemented this same file-path-trusted-location setup with other workbooks that are also stored on OneDrive and that contain VBA code that does not need to run on workbook open, so I know that VBA code is able to successfully run with this setup).
• In the Visual Basic Editor, I have the VBA code in question placed in a "Private Sub Workbook_Open" procedure in the "ThisWorkbook" module of the workbook.
• In the Excel app, we're signed in with the same account under which our files are stored on OneDrive.
• Microsoft 365/Microsoft Office and the Excel app have been updated to their most recent versions.
• Windows 10 (which all five computers run) has been updated to its most recent version.

With that in mind, here are the things that I've tried doing to fix the problem, with basically no success whatsoever:

• In the past, I experienced a similar problem that occurred after I used Find and Replace to replace instances of specific text in a procedure's VBA code: though the VBA code had been working just fine before I used Find and Replace, I found that all of the VBA code in the procedure suddenly returned an error when I attempted to run it, which persisted until I cut all of the VBA code out of the entire module and then pasted it back in again. Thinking that my current problem could be similar to my past problem, I've tried cutting VBA code out of the ThisWorkbook module and pasting it back in, but to no avail.
• I've tried using an Auto_Open procedure in a standard module both instead of and along with the existing Workbook_Open procedure in the ThisWorkbook module. In one test, I moved all of the VBA code from the latter to the former, and in another test, I left the VBA code in the latter and put a Call statement in the former to try to run the VBA code in the latter, but I didn't have any luck changing the outcome either time.
• I've tried repairing Microsoft 365/Microsoft Office using both the "Quick Repair" and "Online Repair" options, the latter of which I believe completely uninstalled Microsoft 365/Microsoft Office and then reinstalled it, but it did not fix the problem.

I need to be able to rely on the fact that the VBA code is going to run every time that these workbooks are opened without fail, no matter which of the five computers they are opened on; I can't just have the VBA code running willy-nilly whenever it feels like it on whichever computer it feels like running it on at that moment.

The only course of action that I could think of to (sort of) circumvent the problem was placing the VBA code in a Worksheet_Calculate procedure in another sheet's module instead of placing it in the Workbook_Open procedure; the sheet contains formulas, so when it's initially calculated at workbook open, the VBA code still, in essence, runs "at workbook open".

However, this is obviously really just a Band-Aid solution, and I would like the Workbook_Open procedure to work as it's supposed to. To further complicate things, the Band-Aid solution isn't even an option for addressing the problem when it occurs with Workbook A, because any module that I could put a Worksheet_Calculate procedure in would be connected to a sheet that would be getting updated and thus recalculated far too frequently for it to be feasible.

The only other "solution" that I've discovered entails restarting the fifth computer after the problem occurs; for whatever reason, when I restart the fifth computer and then open an affected workbook on it, the problem seems to revert back to the stage that I initially described above, where you get to open the workbook and experience the Workbook_Open procedure running normally for maybe a grand total of two times before it starts failing again. Alternatively, the problem may persist even after restarting the computer, so even this "solution" is a tossup.

Even if it were a sure thing, it's not as if I can just instruct users to close everything they have open and restart their entire computer each time that the problem occurs, because even if that wasn't ridiculous, the users really won't even know if the problem has occurred or not given that, under normal circumstances, the execution of VBA code in the Workbook_Open procedure happens in the background when the workbook opens, largely without being seen or noticed by users.

I'm really at a loss as I cannot determine what is causing this issue and I don't know if anyone else might have any input. In the Macro Settings section of the Trust Center, I have both the "Enable Excel 4.0 macros when VBA macros are enabled" and "Trust access to the VBA project object model" checkboxes unchecked; not sure if that has anything to do with the problem I'm having. Could the workbooks or some part of them have possibly gotten corrupted somehow? Possibly due to them syncing between OneDrive and five separate computers? Could it be something contained in the VBA code that's causing the problem? Could it just be an issue with me trying to use VBA code together with OneDrive syncing at all? I've heard that these two features don't always work well together, but I don't have much choice.

I'm experiencing a problem where any VBA code that I have placed in a Workbook_Open procedure in the ThisWorkbook module in any Excel workbook fails to consistently run when the workbook is opened. Weirdly, if I add a Workbook_BeforeClose procedure alongside the Workbook_Open procedure, the VBA code placed in the former does run reliably each time that a workbook is closed, even though the VBA code placed in the latter often fails to.

Every workbook on which I've experienced this problem is stored on OneDrive and synced to five different computers. What's confusing me is that the problem appears to happen only on one of the five computers, with the other four computers appearing to be unaffected somehow; VBA code in a Workbook_Open procedure automatically runs without issue on four of our five computers just as it always has on all five, but recently, it's gotten in the habit of failing to automatically run on the fifth computer.

I've never experienced any problem with VBA code in a Workbook_Open procedure successfully running on any of the five computers, until recently when it stopped running on the fifth computer in particular. Every once in a blue moon, the VBA code may run the first time that a workbook is opened only (possibly even the second time that it's opened if I'm lucky), after which it fails to run with each subsequent opening of the workbook. While using the fifth computer, I can manually run the VBA code while in the Visual Basic Editor and it will run without issue, but for whatever reason, it refuses to run when a workbook is opened.

Although the problem occurs with any Excel workbook that is opened on the fifth computer, it's proving to be especially irritating with two of our workbooks in particular; for the purpose of discussing the problem, I'll refer to them as Workbook A and Workbook B. Workbook A is currently being actively accessed by users on all five computers. Workbook B is currently being accessed by just me on all five computers, but mostly on the fifth computer as I'm currently in the process of developing and debugging Workbook B.

As far as I can tell, everything is set up correctly to allow both the VBA code to run and the two workbooks to sync via OneDrive without issue (the following setup is shared between Workbook A and Workbook B, as well as among all five computers):

• The workbook is saved as a Microsoft Excel Macro-Enabled Worksheet with the .xlsm file extension.
• The macro security settings in the Trust Center are set up as follows:
  • In the Trusted Documents section, I have the "Allow documents on a network to be trusted" checkbox checked.
  • In the Macro Settings section, I have the "Disable VBA macros except digitally signed macros" radio button selected; accordingly, in the Trusted Locations section, I have the "Allow Trusted Locations on my network" checkbox checked, and I have the OneDrive file path to the folder that contains the workbook saved in the list of trusted locations (I've tested and implemented this same file-path-trusted-location setup with other workbooks that are also stored on OneDrive and that contain VBA code that does not need to run on workbook open, so I know that VBA code is able to successfully run with this setup).
• In the Visual Basic Editor, I have the VBA code in question placed in a "Private Sub Workbook_Open" procedure in the "ThisWorkbook" module of the workbook.
• In the Excel app, we're signed in with the same account under which our files are stored on OneDrive.
• Microsoft 365/Microsoft Office and the Excel app have been updated to their most recent versions.
• Windows 10 (which all five computers run) has been updated to its most recent version.

With that in mind, here are the things that I've tried doing to fix the problem, with basically no success whatsoever:

• In the past, I experienced a similar problem that occurred after I used Find and Replace to replace instances of specific text in a procedure's VBA code: though the VBA code had been working just fine before I used Find and Replace, I found that all of the VBA code in the procedure suddenly returned an error when I attempted to run it, which persisted until I cut all of the VBA code out of the entire module and then pasted it back in again. Thinking that my current problem could be similar to my past problem, I've tried cutting VBA code out of the ThisWorkbook module and pasting it back in, but to no avail.
• I've tried using an Auto_Open procedure in a standard module both instead of and along with the existing Workbook_Open procedure in the ThisWorkbook module. In one test, I moved all of the VBA code from the latter to the former, and in another test, I left the VBA code in the latter and put a Call statement in the former to try to run the VBA code in the latter, but I didn't have any luck changing the outcome either time.
• I've tried repairing Microsoft 365/Microsoft Office using both the "Quick Repair" and "Online Repair" options, the latter of which I believe completely uninstalled Microsoft 365/Microsoft Office and then reinstalled it, but it did not fix the problem.

I need to be able to rely on the fact that the VBA code is going to run every time that these workbooks are opened without fail, no matter which of the five computers they are opened on; I can't just have the VBA code running willy-nilly whenever it feels like it on whichever computer it feels like running it on at that moment.

The only course of action that I could think of to (sort of) circumvent the problem was placing the VBA code in a Worksheet_Calculate procedure in another sheet's module instead of placing it in the Workbook_Open procedure; the sheet contains formulas, so when it's initially calculated at workbook open, the VBA code still, in essence, runs "at workbook open".

However, this is obviously really just a Band-Aid solution, and I would like the Workbook_Open procedure to work as it's supposed to. To further complicate things, the Band-Aid solution isn't even an option for addressing the problem when it occurs with Workbook A, because any module that I could put a Worksheet_Calculate procedure in would be connected to a sheet that would be getting updated and thus recalculated far too frequently for it to be feasible.

The only other "solution" that I've discovered entails restarting the fifth computer after the problem occurs; for whatever reason, when I restart the fifth computer and then open an affected workbook on it, the problem seems to revert back to the stage that I initially described above, where you get to open the workbook and experience the Workbook_Open procedure running normally for maybe a grand total of two times before it starts failing again. Alternatively, the problem may persist even after restarting the computer, so even this "solution" is a tossup.

Even if it were a sure thing, it's not as if I can just instruct users to close everything they have open and restart their entire computer each time that the problem occurs, because even if that wasn't ridiculous, the users really won't even know if the problem has occurred or not given that, under normal circumstances, the execution of VBA code in the Workbook_Open procedure happens in the background when the workbook opens, largely without being seen or noticed by users.

I'm really at a loss as I cannot determine what is causing this issue and I don't know if anyone else might have any input. In the Macro Settings section of the Trust Center, I have both the "Enable Excel 4.0 macros when VBA macros are enabled" and "Trust access to the VBA project object model" checkboxes unchecked; not sure if that has anything to do with the problem I'm having. Could the workbooks or some part of them have possibly gotten corrupted somehow? Possibly due to them syncing between OneDrive and five separate computers? Could it be something contained in the VBA code that's causing the problem? Could it just be an issue with me trying to use VBA code together with OneDrive syncing at all? I've heard that these two features don't always work well together, but I don't have much choice.

• excel
• vba

Share Improve this question Follow edited Mar 26 at 19:38 G.D. asked Mar 25 at 20:08 G.D.G.D. 733 bronze badges 1

• Use a tool like Process Monitor (from Microsoft Sysinternals) to observe file access and see if there are any issues with OneDrive locking the files when Excel tries to open them. – user80346 Commented Apr 2 at 22:29

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

Looking at the "Workbook_Open sub won't run when I open the workbook?" question back in 2012.

That worked for me. Save your workbook in .xlsb format. Then open it and save it back in .xlsm format. Workbook_Open() now run as usual.

Save the workbook in .xlsm format, clearing OneDrive's cache, and ensuring the file is fully synchronized before opening on another device.