How to track changes of a shared workbook in a separate workbook

[Feroz90]

Hi All,

Is there any chance to track the changes of a shared workbook in a separate workbook.

Since the workbook needs to be shared with 20 users and adding the code to the same workbook will slow down the file. Since the file consists of 10 excel sheets.

Please help in adding a code to separate workbook to track all the changes with the username, from, to and time.

Thanks

 

[jkpieterse]

That wouldn't help, as the code would still have to work with the workbook in question and hence be running in the same instance of Excel.

 

[gallen]

Does it need to be a separate workbook? A text file would do it, just depends how organised you need the change data.

Something like this works:

Firstly create a text file in a shared location and call it something like ChangeLog.txt

This code goes in the "ThisWorkbook" object

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    RecordChange Target
End Sub


Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    sPreviousValue = CStr(Target.Value)
End Sub

Create a module and put this code in it, making sure you change the LogPath and LogName variables

Public sPreviousValue As String 'ensure this variable declaration is at the top of the module


Sub RecordChange(Target As Range)
    Dim LogPath As String, LogName As String
    Dim sUserName As String
    Dim sLog As String 'log text

    'Make sure you change these variables to suit
    LogPath = "C:\Test\" 
    LogName = "ChangeLog.txt"
    
    'get current, logged in, user
    sUserName = StrConv(Environ("UserName"), vbProperCase)
    
    sLog = sUserName & " | "
    sLog = sLog & "Workbook = " & ThisWorkbook.Name & " | "
    sLog = sLog & "Worksheet = " & ActiveSheet.Name & " | "
    sLog = sLog & "Previous Value = " & sPreviousValue & " | "
    sLog = sLog & "New Value = " & Target.Value & " | "
    sLog = sLog & Now
    
    Open LogPath & LogName For Append As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    
    Print [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] , sLog
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1]#1[/URL] 
    
End Sub

It isn't perfect but it does track changes