Excel macros work locally but not properly from SharePoint (data validation errors)

Hey everyone,

I’m troubleshooting an Excel (.xlsm) file stored in SharePoint that relies on macros, and I’m running into a confusing issue.

Originally, users were getting the standard macro block error:

“Microsoft has blocked macros from running because the source of this file is untrusted”

As a test, I copied the file locally (outside of SharePoint), and everything worked perfectly — macros ran with no issues.

However, when using the file from its SharePoint location (via OneDrive sync), things don’t behave the same:

The macro block error is no longer showing

But users now get this error when entering data:

“This value doesn't match the data validation restrictions defined for this cell.”

It feels like the macros are only partially running or something isn’t fully initializing when the file is opened from SharePoint.

To clarify:

The file must remain in SharePoint (we can’t rely on local copies)

The goal is to have users open and use the file directly from the SharePoint/OneDrive synced location

Other teams in our organization have similar macro-enabled files working from SharePoint, so this should be possible

At this point, I’m not sure if this is:

A macro trust/security issue (still partially restricted?)

A data validation dependency that isn’t loading properly

Or something specific to how the file was built

Questions:

Can macros fully run from SharePoint/OneDrive synced locations, or are there limitations?

Has anyone seen data validation errors like this caused by macros not executing fully?

What’s the proper way to make this work — trusted locations, macro signing, or policy changes?

Appreciate any guidance — this one’s been tricky to pin down.

Thanks!