Shared/Co-authored workbooks

[Mrock1]

I have a desire to share two workbooks that work in concert with one another. One largely contains the macros that drive the application and the other the data created and used by the application workbook. I need to be able to have the same pair of workbooks open on two machines such that when one machine updates the data, the other machine is triggered to update itself to reflect the changes made in the data. I've discover and created a Workbook_AfterRemoteChange subroutine in the ThisWorkbook module of the data file and shared both workbooks on a OneDrive folder so that both machines can load them.

The workbook_AfterRemoteChange code is as follows:
Private Sub Workbook_AfterRemoteChange()
Dim DataFileName As String
Dim FileName As String
DataFileName = ThisWorkbook.Name
DataFileName = Left(DataFileName, InStr(DataFileName, ".") - 1)
FileName = Left(DataFileName, InStr(DataFileName, "_Data") - 1) & ".xlsm"
Application.Run ("'" & FileName & "'!DataFileChangedRemotely")
End Sub

I still find two problems from time to time.
1. I get an error on one or both machines when the second machine opens the workbook indicating that the upload wasn't possible and prompting me to save a copy or discard my changes. Note: The application workbook is the one the user opens and the data workbook is opened by the application workbook's Workbook_Open macro.

2. When one machine actually makes a change to the data workbook initiated by a macro on a userform sourced from the application workbook, the AfterRemoteChange doesn't seem to trigger. I do have the data workbook minimised and it's window invisible (Window(data workbook).visible = false.

I am trying to use the collaboration sharing method, but because this is messy as an uninitiated user might have to know how to share the workbooks to their other machine (possibly with an alternate OneDrive account), I don't think it's an ideal method. Perhaps the classic file sharing might be better, but I don't know if AfterRemoteChange triggers at all with this method.

As for the second issue, I thought that, despite autosave being enabled, I might be able to force the data update to trigger the AfterRemoteChange on the other machine if I was to execute a workbook.save after every data workbook update.

I'd appreciate any help with this challenge.

Many Thanks
Max

 

[Mrock1]

Update:

This is purely empirical but in my application, I keep the data workbook both minimized (Windows(DataFilename).WIndowState = xlMinimized) and invisible (WIndows(DataFilename).visible = False). At least for now I've discovered that the sync works, and therefore the Workbook_AfterRemoteChange is triggering) when the data workbook is visible and displaying on the desktop (haven't tried with it minimized as I ran out of time).

However, with the AfterRemoteChange triggering, I then get a ping-pong with the Workbook_AfterRemoteChange on the data workbook (only place it exists), that continues ad-infinitum. I haven't looked, but I think I have something in the subroutine that is subsequently triggered by the AfterRemoteChange routine that trivially updates the data filename (from memory, I think I am deleting and recreating some named ranges, which may be triggering the AfterRemoteChange in that ping-pong manner).

It's interesting that Excel doesn't seem to sync the workbook if it's invisilbe and/or minimized, which doesn't make sense, but it could just be one of those things in the implementation of co-authoring - an oversight or undocumented feature, if you like.