How to Run VBA Macros in an Excel Workbook Stored on SharePoint
Question details
The user needs to know how to execute VBA macros in an .xlsm Excel file hosted on SharePoint without encountering read-only restrictions or co-authoring conflicts.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Opening and executing a macro-enabled workbook (.xlsm) that is stored on a shared SharePoint server.
- Observed behavior
- Macros fail to run if the file opens in Excel for the Web, is in read-only mode, or if multiple users are attempting to co-author the macro-enabled workbook simultaneously.
Ensure you have the desktop version of Microsoft Excel installed on your device, as VBA macros cannot be executed in the web version of Excel. Additionally, verify that your SharePoint permissions allow you to edit the file.
Open the Workbook in the Excel Desktop Application
VBA macros require the full desktop version of Excel and exclusive editing rights to run properly.
SharePoint defaults to opening files in Excel for the Web, which does not support VBA macros. Additionally, macro-enabled workbooks (.xlsm) do not support Excel's co-authoring feature. If another user is actively editing the file, it will open in read-only mode, which prevents macros from running.
Navigate to your .xlsm file in your SharePoint document library using your web browser.
Click the three dots (ellipses) next to the file name to open the context menu, select 'Open', and then choose 'Open in app' to launch the file in the Excel desktop application.
If prompted with a security warning at the top of the spreadsheet, click the 'Enable Content' button to allow the VBA macros to execute.
Add SharePoint to Excel's Trusted Locations
Ensure Excel does not block macros from your SharePoint network location by adding it to your Trust Center settings.
A Lightweight and Compatible Alternative for Your Spreadsheets
If SharePoint co-authoring limitations and complex macro restrictions in Microsoft Office are slowing you down, consider trying WPS Office. It is a free, lightweight, and highly compatible suite that supports Microsoft Excel formats natively, providing a seamless and efficient spreadsheet experience.
- 1. Download WPS Office: Visit the official WPS Office website to download the free suite and install it on your computer.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheets and open your Excel files directly.
- 3. Edit with Ease: Enjoy editing your documents with a familiar interface and save your work securely.
Frequently Asked Questions
Why do I get a read-only error when opening a macro-enabled workbook from SharePoint?
This error occurs when another user is currently editing the file. Macro-enabled workbooks (.xlsm) do not support the Excel co-authoring feature. Only one person can edit the file and execute its macros at any given time.
Can I run VBA macros in Excel for the Web?
No, VBA macros are not supported in Excel for the Web. You must open the workbook in the Microsoft Excel desktop application to enable content and run any VBA code.
Why are my Workbook_Change events not triggering after downloading from SharePoint?
Ensure that your Workbook_Change event procedures are placed correctly inside the 'ThisWorkbook' module in the VBA editor. If they are placed in a standard module or a specific worksheet module, the workbook-level events will not trigger.
