Consulting

Results 1 to 8 of 8

Thread: Co-Authoring Issues - Auto-Updating

  1. #1
    VBAX Regular
    Joined
    Dec 2015
    Posts
    10
    Location

    Co-Authoring Issues - Auto-Updating

    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

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    Moderator Bump

    Interesting Question.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Dec 2015
    Posts
    10
    Location
    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.

  4. #4
    VBAX Master paulked's Avatar
    Joined
    Apr 2006
    Posts
    1,007
    Location
    Hi

    I had a similar problem and this https://superuser.com/questions/4401...ng-and-reopeni got me sorted.

    Cheers

    Paul Ked
    Semper in excretia sumus; solum profundum variat.

  5. #5
    VBAX Regular
    Joined
    Dec 2015
    Posts
    10
    Location
    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.

  6. #6
    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.
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  7. #7
    VBAX Regular
    Joined
    Dec 2015
    Posts
    10
    Location
    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?

  8. #8
    VBAX Regular
    Joined
    Dec 2015
    Posts
    10
    Location
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •