Results 1 to 3 of 3

Thread: How to track changes of a shared workbook in a separate workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2019
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    Board Regular jkpieterse's Avatar
    Join Date
    Dec 2007
    Location
    Weert
    Posts
    900
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to track changes of a shared workbook in a separate workbook

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

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    Board Regular gallen's Avatar
    Join Date
    Jun 2011
    Location
    Manchester UK
    Posts
    1,899
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to track changes of a shared workbook in a separate workbook

    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

    Code:
    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

    Code:
    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 #1 
        
        Print #1 , sLog
        Close #1 
        
    End Sub
    It isn't perfect but it does track changes

    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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