title: What is a good work-around for co-authoring issues with vba macros on excel
body: <p>I have designed a macro enabled workbook that is saved on OneDrive that shows a daily roster and task list for employees at our company. The workbook can add tasks or mark them as completed on the multiple computers it can be opened on. As I discovered soon after making this workbook, the co-authoring feature is not compatible with macro enabled workbooks, so if the file is opened on multiple computers the error message comes up saying that changes cannot be saved because of unsupported feature changes made by someone else. The employees get confused and annoyed with it and end up saving multiple copies of the file with each individual change they make which is fairly inconvenient. </p>
  
  <p>I learnt that simply turning off autosave and therefore the co-authoring feature when opening it up will prevent issues, however, if the file is opened with autosave turned off, it is unable to be opened by any other users. It is not entirely necessary that the file needs to be open on multiple computers at once, but it would be nice if there is a solution for that. What is necessary though is the ability to be able to switch who has the file open, so that if someone leaves it open on a computer and someone else wants to open it on their computer, it will open and then on the previous users computer it would turn autosave off, or close the file or just send a notification to prevent errors popping up on the other users computer.</p>
  
  <p>Below is some of the code blocks where I attempt to take advantage of co-authoring and use a cell change to detect when another computer opens up the file and use that event to turn off autosave to prevent the error messages showing up. As you can guess this runs into the problem that the code itself that changes the cell triggers the error message "Unsupported feature changes made by someone else" before it can prevent it. </p>
  
  <pre><code>Module 1: Public TOmsgShown As Boolean
  
  Private Sub Workbook_Open()
      TOmsgShown = False
      Dim currentuser As Range
      Set currentuser = Sheets(1).Cells(1, 100)
      If IsEmpty(currentuser) = False Then
          userswitch = MsgBox(currentuser.Value &amp; " has currently got this workbook open, and it is only able to be edited by one person at a time. " &amp; _
          "Do you want to take over editing capabilities? No to open this as read-only (any changes made will not be saved) or Yes to force " &amp; currentuser.Value &amp; " to switch to read-only.", vbYesNo)
          If userswitch = vbNo Then
              ThisWorkbook.ChangeFileAccess (xlReadOnly)
              ThisWorkbook.AutoSaveOn = False
          Else
              currentuser.Value = Application.UserName
          End If
      Else
          currentuser.Value = Application.UserName
      End If
      ThisWorkbook.Save
  End Sub
  
  Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
       If Sheets(1).Cells(1, 100).Value &lt;&gt; Application.UserName And TOmsgShown = False Then
          Application.DisplayAlerts = False
          ThisWorkbook.AutoSaveOn = False
          ThisWorkbook.ChangeFileAccess (xlReadOnly)
          TOmsgShown = True
          MsgBox (Sheets(1).Cells(1, 100).Value &amp; " has opened up this roster and taken over editing capabilities. You will now be switched to Read-Only mode and can not save any changes made. Re-open this workbook to continue editing.")
          Cancel = True
      End If
  End Sub
  </code></pre>
  
  <p>Are there any vba events that can be used that won't trigger the error to make this method work, or otherwise, is there a way I can use code to deal with the error message directly so that it won't show and prompt employees to save a copy file unnecessarily?</p>