You cannot save changes to an Excel workbook that is currently in shared mode. Microsoft explicitly disables the Save function for macro-enabled workbooks (.xlsm) when multiple users have access, as simultaneous edits would corrupt the VBA project and data integrity. To resolve this, you must either disable sharing before saving or migrate to a cloud-based collaboration solution like OneDrive or SharePoint that supports concurrent editing without compromising macro security.
Understanding the Shared Mode Restriction
When you enable tracking of changes in an older version of Excel (File > Info > Protect Workbook > Track Changes > Highlight Changes), the file enters "Shared" mode. In this state, Excel locks certain features to prevent conflicts between users. Specifically, if your workbook contains Visual Basic for Applications (VBA) modules, classes, or forms, Excel prevents any user from saving the file because the binary structure of the macro component cannot be safely merged across multiple instances. This is not a bug but a deliberate architectural limitation designed to protect the integrity of the codebase. If you attempt to save while in this mode, Excel will display an error message stating that the file cannot be saved because it is shared.
Distinguishing Between Legacy Sharing and Modern Co-Authoring
It is crucial to distinguish between legacy shared workbooks and modern co-authoring via Microsoft 365. Legacy sharing uses a local file lock mechanism where only one person can edit at a time unless specific settings are applied, and it fundamentally breaks with macros. Modern co-authoring stores the file on OneDrive or SharePoint, allowing multiple users to edit cells simultaneously. However, even in modern environments, direct editing of VBA code remains restricted to a single user at a time due to the complexity of compiling and storing code objects. Therefore, the inability to save is often triggered by the legacy shared setting rather than the cloud storage location itself.
Step-by-Step Resolution: Disabling Shared Mode
The most direct way to regain the ability to save your macro-enabled file is to turn off the shared workbook feature. Follow these steps carefully to ensure no data is lost during the transition.
- Open the File: Launch Excel and open the workbook that is giving you trouble.
- Navigate to Review Tab: Click on the
Review tab in the ribbon menu at the top of the window.
- Locate Share Settings: Look for the
Share Workbook button. In newer versions of Excel, this might be under Protect > Share Workbook.
- Disable Sharing: In the dialog box that appears, uncheck the box that says
Allow changes by more than one user at the same time. Alternatively, look for a button labeled Unshare Workbook if available in your version.
- Save Immediately: Once shared mode is disabled, the file is no longer locked for macro operations. Press
Ctrl + S or click the Save icon immediately. You may need to save it as a new filename to avoid overwriting the original shared instance.
After completing these steps, verify that you can now edit and save VBA modules without error. Note that disabling sharing means other users will lose access until you re-share the file, so coordinate with your team before making this change.
Alternative Approach: Using Cloud Storage
If your goal is to maintain collaboration while using macros, consider migrating the file to OneDrive or SharePoint. This approach leverages modern co-authoring capabilities. However, keep in mind that VBA projects still require exclusive access for editing. You can collaborate on cell data and formulas simultaneously, but if two people try to edit the VBA editor at the same time, one will be blocked. This method is generally safer and more stable than legacy shared workbooks. For related issues involving formula references in collaborative sheets, you might find guidance on
Changing tab name in SUMIF formula not working helpful, as sheet name changes can impact macro references.
Backup Before Modifying
Before attempting to disable sharing or move files, always create a backup copy. Use
File > Save As to create a duplicate with a different name, such as
Workbook_Backup.xlsm. This ensures that if the conversion process fails or data becomes corrupted, you retain a pristine version of your original work.
Common Pitfalls and Best Practices
Many users encounter errors after turning off sharing because they do not save the file correctly. Ensure you save the file as
.xlsm (Macro-Enabled Workbook) rather than
.xlsx, which strips all VBA code. Additionally, check for external links to other shared workbooks, as these can sometimes cause saving issues even after sharing is disabled. If you continue to face issues, inspect your VBA project for broken references or missing libraries.
For users experiencing unexpected restarts or crashes during these operations, reviewing stability fixes can be beneficial. See
How to Stop Excel from Restarting Itself When Closing for troubleshooting tips on application stability. Avoid relying on third-party tools to bypass these restrictions, as they can introduce security vulnerabilities and corrupt your macro code.
Frequently Asked Questions
Can I share a macro-enabled workbook without losing the macros?
Yes, but you must use modern co-authoring via OneDrive or SharePoint rather than legacy shared workbooks. While cell data can be edited by multiple users, VBA code editing requires exclusive access, meaning only one person can modify the code at a time.
Why does Excel ask me to save as .xlsb instead of .xlsm?
Excel does not force this conversion; however,
.xlsb (Binary Workbook) is more efficient for large datasets and supports macros. If you see this prompt, it is likely because you are trying to save a file that exceeds standard limits or you selected the wrong format. Always choose
.xlsm to preserve VBA code compatibility.
Is it safe to use VBA password removal tools?
No, using third-party tools to remove VBA passwords is risky and can corrupt the project structure. These tools often exploit vulnerabilities that may trigger security warnings or fail silently, leading to data loss. Always rely on official Microsoft methods or professional backup strategies.
What happens to my changes if I disable sharing?
Disabling sharing removes the conflict resolution layer that tracks individual user edits. Any pending changes that haven't been merged into the main file may be lost or overwritten. Always save your current work and ensure all collaborators have submitted their updates before turning off sharing.
Can I use Power Automate to handle macro saves in shared files?
Power Automate can automate workflows around Excel files, but it cannot bypass the fundamental restriction that shared workbooks cannot save VBA changes directly. It is better suited for triggering actions based on cell changes rather than managing file structure conflicts.