This is a classic case of a tool fighting the environment it was designed for, and the frustration is entirely warranted. The user's problem, macros running perfectly on a local copy but breaking when the file lives in SharePoint, is not a random glitch. It is a direct consequence of how modern cloud-synced platforms handle file access, and the fact that the macro block error disappeared only to be replaced by a data validation failure tells us the issue is execution timing, not trust. The user is right to suspect something isn't fully initializing.
Here is what is happening. When Excel opens a file from a OneDrive-synced SharePoint location, it does not treat the file exactly as it would a local copy. The application has to negotiate with the sync engine, which can introduce delays in how the workbook's components load, especially VBA projects and their associated event handlers. A macro that assumes the workbook is fully present and all data validations are registered before it runs will fail silently when those dependencies haven't finished loading. The data validation error is the symptom: the macro attempted to write or validate data against rules that were not yet active, because the workbook's initialization sequence was interrupted by the sync layer. This is not a security block in the traditional sense; it is a race condition between the cloud sync and the VBA engine.
The user's observation that other teams have similar files working is helpful but not a guarantee. Those files may have been built with different event triggers, or they may rely on macros that execute after the workbook is fully loaded rather than on startup. The fix here is not to abandon SharePoint but to redesign how the macros are triggered. The proper approach is to move critical initialization code out of the Workbook_Open event and into a manual activation step, a button, a ribbon command, or a named range that users click after the file is fully synced. This removes the timing dependency. Additionally, signing the macro with a trusted certificate and adding the SharePoint URL to the Trusted Locations list will eliminate the security warnings that originally appeared, but those steps alone will not fix the race condition. The user must also ensure that any data validation rules are applied programmatically after the workbook is open, not assumed to be present from the saved state.
The practical takeaway is this: SharePoint and OneDrive are not broken for macros, but they demand a different development discipline. You cannot treat a cloud-hosted file the same as one on a local drive and expect identical behavior. The solution is to decouple macro execution from workbook opening, use signed code to satisfy trust requirements, and test the file exclusively from the SharePoint path during development. The user's goal is achievable, but only if they stop looking for a security policy workaround and start addressing the real culprit: the assumption that the cloud is invisible to the code.