To address the synchronization issues in a Macros-Enabled Workbook accessed by multiple users via OneDrive share link, follow these steps:
-
Disable Macros: Disable the macros in the workbook to prevent errors and conflicts when multiple users access the file. To do this, go to the
Developertab, clickMacros, select the macro, and clickDisable. -
Use Protected View: Enable Protected View to prevent users from enabling macros. In OneDrive, click
Settings, go toViewing options, and selectBlock editing, require checkout, and open in Protected View. -
Share the Workbook as Read-Only: Set the workbook as read-only to prevent users from making changes that could cause synchronization issues. In OneDrive, right-click the file, select
Manage access, and clickAdvanced. UnderPermissions, clickEdit, and set the permissions toRead. -
Use Excel Services: If you need to allow users to interact with the workbook, consider using Excel Services in SharePoint. This allows users to view and interact with the workbook in their web browser, reducing synchronization issues.
-
Use Excel Online: Excel Online is a cloud-based version of Excel that can be accessed via a web browser. It allows multiple users to work on the same workbook simultaneously without synchronization issues.
-
Avoid Using Volatile Functions: Volatile functions, such as
RAND()andTODAY(), recalculate every time the workbook is opened. This can cause synchronization issues when multiple users access the file. Instead, use non-volatile functions, such asINDIRECT(), or calculate the values you need before saving the workbook. -
Use External Data Connections Wisely: External data connections can cause synchronization issues if the data source changes. If you need to use external data connections, consider using Power Query to import the data into the workbook.
-
Use Version History: OneDrive automatically saves previous versions of the file. If a user makes changes that cause issues, you can revert to a previous version. To access version history, right-click the file, select
Version history, and choose the version you want to restore. -
Use Excel Table Features: Excel tables can help manage data and reduce synchronization issues. To create a table, select the data, click
Format as Tablein theStylesgroup, and choose a table style. -
Use Excel Workbook Recovery: If the workbook becomes corrupted, you can use Excel Workbook Recovery to try and recover the data. To do this, open a new workbook, click
File, selectOpen, clickBrowse, select the corrupted workbook, and clickOpen Recovery.
References: