VBA Track changes on shared workbook

[Michiko]

Hello Everyone,

I have a VBA that copies a given sheet from my workbook.xlsm to a new workbook.xlsx and saves it in a shared folder. It works just fine, but I want it to enable track changes on the new workbook.xlsx before closing it.

Since I'm not an expert on code, nor whatsoever!, I've just searched for the piece of code online. However, I've already tried a lot of combinations and none worked, I always get an error. Currently, I'm using this:

With ActiveWorkbook
  .HighlightChangesOptions _
  When:=xlAllChanges, _
  Who:="Everyone"
  .ListChangesOnNewSheet = False
  .HighlightChangesOnScreen = True
End With

And getting this:

(Yes... The code gets highlighted... Just not the kind of highlight I want ?)

Can anyone help, please?

Thanks!

 

[Michiko]

Also: if I just save the workbook.xlsx and open it, I can see that the 'Track Changes (Legacy)' feature isn't available:

I think it might be the reason, but I don't know how to make it available before enabling it (through the VBA code).

 

[GWteB]

Changes can only be tracked in shared workbooks. This is an Excel feature and is not the same as a shared folder on disk.
At the point a workbook's access mode is changed to shared (as opposed to the default, which is exclusive) the workbook has to be saved again in order to make it actually effective.
So after saving the new workbook in the shared folder you should be able to change its access mode and also keep track of changes other users make to the workbook. Your code could look like below and you might consider to take a look over here ...

How to Enable and Use Track Changes in Excel

Do you know you can track changes in Excel - just like MS Word. Learn how to enable and use this option while collaborating with others.

trumpexcel.com

Sub Michiko()

    Const NEWFILE As String = "Z:\Users\SharedFolder\YourWorkbook.xlsx"         ' <<< change file name to suit

    Dim wbNew   As Workbook

    ThisWorkbook.Sheets("Sheet1").Copy                                          ' <<< change sheet name to suit
    Set wbNew = ActiveWorkbook

    With wbNew
        .SaveAs Filename:=NEWFILE, FileFormat:=xlOpenXMLWorkbook, AccessMode:=xlShared

        .KeepChangeHistory = True
        .HighlightChangesOptions When:=xlAllChanges, Who:="Everyone"
        .ListChangesOnNewSheet = False
        .HighlightChangesOnScreen = True
        .Save
        .Close
    End With
End Sub

 

[Michiko]

@GWteB Thank you very much for your help. However, it's still not working.

Actually, I already had the AccessMode:=xlShared embedded on the code when saving the sheet as a new workbook.

Any other idea?

 

[GWteB]

Please elaborate on "it's still not working". Any warning or error message and if so, on which line within the code does that occur?
Additional questions are: Is that particular worksheet protected? Does its code-behind module contain any code?

 

[Michiko]

I get the exact same error I did. All .saveas syntax gets highlighted.

    With wbNew
        .SaveAs Filename:= _
        "https://company.sharepoint.com/sites/Company/Shared%20Documents/OPS%20Activity%20Report.xlsx" _
        , FileFormat:=xlOpenXMLWorkbook, AccessMode:=xlShared

        .KeepChangeHistory = True
        .HighlightChangesOptions When:=xlAllChanges, Who:="Everyone"
        .ListChangesOnNewSheet = False
        .HighlightChangesOnScreen = True
        .Save
        .Close
    End With

And no, the worksheet isn't protected.

 

[GWteB]

Try converting the url to a proper path, like:

"\\company.sharepoint.com\sites\Company\Shared Documents\OPS Activity Report.xlsx"

 

[GWteB]

I have a VBA that copies a given sheet from my workbook.xlsm to a new workbook.xlsx and saves it in a shared folder. It works just fine, but ....

Seems you are talking about different issues here

 

[Michiko]

Still getting exactly the same error and part of the code highlighted.

If I remove AccessMode:=xlShared it only stumbles on .HighlightChangesOptions When:=xlAllChanges, Who:="Everyone"

 

[Michiko]

Seems you are talking about different issues here

It works fine when I omit the track changes code (including the explicit sharing option).