It’s 3:12 PM. You just uploaded Q3_Sales_Report.xlsm to your team’s SharePoint site. Sarah Chen clicks it from the browser — and gets a blank gray screen. She refreshes. Tries Edge, then Chrome. Still nothing. Meanwhile, your macros for auto-refreshing pivot tables and emailing summaries sit completely dormant.
The Problem
SharePoint doesn’t block .xlsm files outright — it accepts them, displays their metadata, and even lets you download them. But when opened in the browser, Excel Online (the web version embedded in SharePoint) simply ignores all VBA code. No warning. No error message. Just silence.
This isn’t a permissions glitch or a browser cache issue. It’s baked into Microsoft’s architecture: Excel Online is sandboxed, JavaScript-only, and deliberately excludes VBA execution for security and cross-platform consistency.
| Symptom | Cause | Fix |
|---|---|---|
| Clicking file opens blank Excel Online tab | VBA triggers no UI response in browser | Open in desktop Excel via "Edit in Excel" |
| Auto_Open() macro doesn’t run on open | Excel Online disables all workbook_open events | Replace with Power Automate flow triggered on file update |
| "Enable Content" prompt never appears | No macro security model exists in Excel Online | Set up Trusted Locations in desktop Excel for automatic enablement |
| Buttons tied to macros do nothing on click | Form controls & ActiveX are stripped in web view | Switch to Excel ribbon buttons or use Office.js add-ins |
| File shows as "Read-only" despite having edit rights | SharePoint enforces co-authoring lock if macros are present | Remove macros before upload — or use OneDrive for Business sync instead |
The Solution
You don’t need to abandon your macros — you just need to change how users access them. Here’s what actually works:
- Upload normally: Save your file as
Q3_Sales_Report.xlsm, then drag-and-drop into your SharePoint document library. Don’t rename it or convert formats. - Disable default browser opening: Go to your SharePoint library settings → Advanced Settings → Under "Opening Documents in the Browser", select "Open in the client application". This forces Excel desktop to launch automatically.
- Configure Trusted Location: On each user’s machine, open Excel → File → Options → Trust Center → Trust Center Settings → Trusted Locations → Add your SharePoint library URL (e.g.,
https://acmecorp.sharepoint.com/sites/finance/Shared%20Documents/). Check "Subfolders of this location are also trusted". - Add a visual cue: In cell A1 of your workbook, insert this formula:
=IF(ISERROR(FIND(".sharepoint.com",CELL("filename"))),"✅ Running locally","⚠️ Open in Desktop Excel")
This alerts users if they’re viewing in Excel Online.
After those steps, here’s what users see — and what they get:
| Action | Before Fix | After Fix | Cell Reference |
|---|---|---|---|
| Click file in SharePoint | Blank Excel Online tab | Desktop Excel launches, macros run | A1:A3 |
| Open Auto_Open() | Never executes | Runs at startup (if enabled) | ThisWorkbook module |
| Click "Refresh Dashboard" button | Button invisible or unresponsive | Triggers RefreshDashboard() sub |
B12 on Sheet1 |
| Save changes | "Save As" forced, loses SharePoint link | Direct save back to SharePoint library | Ctrl+S works normally |
| User sees security warning | "Enable Content" prompt every time | No prompt — runs silently | Trust Center > Macro Settings |
Going Further
If your team uses Excel on Mac or shared kiosks where installing desktop Excel isn’t possible, consider these alternatives:
- Power Automate + Excel REST API: Trigger flows when cells change in B2:C10 (e.g., status updates), then call external services or send emails — no VBA needed.
- Office.js Add-in: Build a lightweight task pane add-in that replaces your most-used macros. It runs in both desktop and web versions. Sample manifest references
https://acmecorp.sharepoint.com/sites/finance/Scripts/macro-replacer.html. - Hybrid trigger sheet: Keep one clean .xlsx version for web use, and maintain a separate .xlsm version with identical structure (same named ranges, same sheet order). Use Power Query to sync data between them daily.
Surprising tip: You can embed working macros in SharePoint-hosted Excel files — but only if users open them via the OneDrive for Business sync client. Files synced to C:\Users\Sarah\OneDrive - Acme Corp\Finance\ behave like local files, even though they’re backed by SharePoint. That’s often faster than clicking through the web interface.
When NOT to Use This
Don’t force macro-enabled Excel into SharePoint if:
- Your organization uses Microsoft 365 Business Basic (no desktop Excel license included).
- Users regularly access files from iPads or Chromebooks — desktop Excel won’t launch.
- You rely on
SendKeys,Shell, or any Windows API calls — those fail outside Windows desktop Excel. - Your macro modifies the ribbon UI (
CommandBars) — unsupported in modern Excel versions regardless of platform. - You’re storing sensitive credentials inside VBA modules — SharePoint audit logs won’t capture macro-triggered actions.
If any of those apply, shift to Power Automate, Power Apps, or Azure Functions — not VBA.
Keyboard Shortcuts
| Action | Windows Shortcut | Mac Shortcut | Notes |
|---|---|---|---|
| Open Trust Center | Alt + T + O |
Cmd + , → Security |
Then go to Trust Center Settings |
| Toggle macro recording | Alt + T + M + R |
Fn + Opt + F8 |
Useful for quick debugging in desktop Excel |
| Run macro directly | Alt + F8 |
Fn + Opt + F8 |
Then select and Run — works even if Auto_Open fails |
| Force desktop open from SharePoint | Alt + Click file name |
Not supported | Hold Alt while clicking file — bypasses browser open |
