Co-Authoring Issues - Auto-Updating

[didless]

Good evening all,
I am looking into rolling out a sharepoint adoption at our company, using the co-authoring capabilities of Excel 2016 with OneDrive/Sharepoint to enable sharing of macro-enabled workbooks with userforms etc (at last we can do this!)
I am however hitting a snag with worksheet_change events. I have some of these which I use to detect when values are entered in certain columns and then they will automatically populate other columns with data (such as current user, today's date etc).
I have managed to get to a point where if 2 users are signed in at the same time and one user enters data in one column (triggering the change event and therefore adding data in another column), this gets updated to the cloud and the 2nd user sees an update for the initial data that is entered (the one that triggered the event) but then nothing more is updated (the additional data that was inputted by the change event), although this appears OK on the 1st user's version. The workbook for the 2nd user will then generally stop updating and the 'Refresh' button appears at the top, saying that there is a more recent version uploaded to the cloud. The 2nd user then has to click this to close and reopen the workbook to see the same version as the 1st user.
Does anyone know of any way to make this work? If you need any further clarification on what I'm talking about please feel free to ask!

Thanks
Dave

[SamT]

Moderator Bump

Interesting Question.

[didless]

Another similar issue I have also noticed is that when any text is added to the worksheet from a userform, I again get the Refresh/more recent workbook uploaded message on the 2nd users workbook.

[paulked]

Hi

I had a similar problem and this https://superuser.com/questions/440187/is-there-a-way-to-get-a-shared-spreadsheet-to-update-without-closing-and-reopeni got me sorted.

Cheers

Paul Ked

[didless]

Hi Paul,
Thanks for the link. I don't think this will work unfortunately as this was using the old Shared Workbook functionality in Excel. I am setting this up using the newer Sharepoint Co-Authoring abilities in Excel 2016. As such, I don't seem to have the options to select as highlighted in the example of Excel 2010. :(

[Jan Karel Pieterse]

Odd, I tried with a very basic change event that adds a time stamp to the cell to the right of the edited cell and it did propagate the timestamp to the other workbooks.

[didless]

Thanks Jan,
I am starting to think that it is just the workbook that I am working with. I have also successfully tested this in a basic workbook and it works fine. The same with the userform issue.
For some reason, this workbook doesn't display the other user's names at the top right hand corner when co-authoring the file. Any ideas why?

[didless]

I think I've identified the issue... The sheets that were being edited by either the change event or userform input were password protected, and so in the code the sheet was being unprotected, edited and then reprotected again. It seems that co-authoring doesn't like any structural/protection changes like this and so was having issues. Removing the protection makes the co-authoring work.
This obviously raises other issues, but I think as a workaround I will use a sheet change event to block users from editing columns that they are not currently allowed to edit.