Skip to main content

How to track changes without sharing workbook?

Author: Xiaoyang Last Modified: 2020-07-16

Normally, when you apply the Track Changes feature in Excel, the workbook will be shared at the same time, this will be annoying because some of the features in Excel will be disabled. How could you track changes without sharing workbook? Here, I will recommend a VBA code for you.

Track changes without sharing workbook with VBA code

arrow blue right bubble Convert text to table in Outlook

There is no direct way for you to solve this problem, but, you can apply a flexible VBA code to solve it, please do as follows:

1. Right click at the sheet tab that you want to track changed cells, and choose View Code from the context menu, in the popped out Microsoft Visual Basic for Applications window, please copy and paste the following code into the blank Module:

VBA code: Track changes without sharing workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
  'Updateby Extendoffice
    Const xRg As String = "A1:Z1000"
    Dim strOld As String
    Dim strNew As String
    Dim strCmt As String
    Dim xLen As Long
    With Target(1)
        If Intersect(.Cells, Range(xRg)) Is Nothing Then Exit Sub
        strNew = .Text
        Application.EnableEvents = False
        strOld = .Text
        .Value = strNew
        Application.EnableEvents = True
        strCmt = "Edit: " & Format$(Now, "dd Mmm YYYY hh:nn:ss") & " by " & _
        Application.UserName & Chr(10) & "Previous Text :- " & strOld
        If Target(1).Comment Is Nothing Then
            xLen = Len(.Comment.Shape.TextFrame.Characters.Text)
        End If
        With .Comment.Shape.TextFrame
            .AutoSize = True
            .Characters(Start:=xLen + 1).Insert IIf(xLen, vbLf, "") & strCmt
        End With
    End With
End Sub

doc track change without sharing 1

Note: In the above code, A1:Z1000 is the data range that you want to track changes.

2. Then save and close this code window, now, when you change the values in any cells within the specified range you set in the code, the cells will be tracked, and the workbook is not be shared. See screenshot:

doc track change without sharing 2

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (14)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Does this MAcro capture changes on formula values everytime my data model is updated?
This comment was minimized by the moderator on the site
Is there a way to record the changes for all tabs in a workbook, and record the changes to a new spreadsheet? THAT would be awesome ... and I can't find any information across the internet with this type of tracking process.
This comment was minimized by the moderator on the site
Hello, jfjoyner,To change this code for all sheets, and record the track changes to another sheet, please use the below code:
Note: Please put this code into the ThisWorkbook module.
Option Explicit
Dim mStrRgAddress As String
Dim mStrRgValue As String

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Const xRg As String = "A1:Z1000"
Const xSheetName As String = "Record sheet" 'The sheet that you want to put the track changes, please change the sheet name to your own.
Dim strOld As String
Dim strNew As String
Dim strCmt As String
Dim xLen As Long
Dim xSheet As Worksheet
Dim xRgCell As Range
Dim xRgCell2 As Range
On Error Resume Next
Set xSheet = Application.Sheets.Item(xSheetName)
If mStrRgAddress <> "" Then
Set xRgCell = Range(mStrRgAddress)
If xRgCell.Text <> mStrRgValue Then
strCmt = mStrRgAddress & " : " & Format$(Now, "dd Mmm YYYY hh:nn:ss") & " by " & _
Application.UserName & Chr(10) & "Previous Text :- " & mStrRgValue
Set xRgCell2 = xSheet.Range("a1048576").End(xlUp)
If xRgCell2.AddressLocal = xSheet.Range("A1").AddressLocal Then
If xRgCell2.Value <> "" Then
Set xRgCell2 = xRgCell2.Offset(1, 0)
End If
Set xRgCell2 = xRgCell2.Offset(1, 0)
End If
xRgCell2.Value = strCmt
End If
End If
If xSheet.Name = Sh.Name Then Exit Sub
mStrRgValue = Target.Text
mStrRgAddress = Target.AddressLocal(False, False, , True)
End Sub
This comment was minimized by the moderator on the site
Thanks, this is great, but I can't get it to work. I assume it runs automatically, meaning no need to click on "Run"? I renamed a worksheet to say {--TRACK_CHANGES--} to follow the instructions you left in green. So far, it is not recording anything. Thanks.
This comment was minimized by the moderator on the site
Hello jfjoyner3,First, after you copy our VBA Code into the ThisWorkbook module, the code runs automatically.Second, our VBA code does works. Plesae see the two screenshots I uploaded in this comment.
This comment was minimized by the moderator on the site
Mandyzhou, Thank you again for this detailed guidance. I am getting a Syntax Error and it points me to this line:

If mStrRgAddress <> "" Then

Are you able to guide to modify this and remove the Syntax Error?
Thank you!
This comment was minimized by the moderator on the site
Thanks again, skyyang.I also noticed that when I pasted this into the module for This Workbook, my large spreadsheet began to calculate continuously and would not stop.  I know nothing about VBA programming, but I found this discussion about the same topic. It might explain why the calculation goes on endlessly. 
The issue on this web site was: <span style="letter-spacing: 0.2px; color: inherit; font-family: inherit; font-style: inherit; font-variant-ligatures: inherit; font-variant-caps: inherit;">But once the continuous-calculation issue starts, it's persistent. Pressing the Esc key does interrupt the calculation, but it starts right back up again unless I switch to Manual Calc. Manual calc mode works fine until I run any of the macros, which then ends by re-enabling AutoCalc... I've been through each worksheet in the model and run error-checking to no avail.</span>
Their conclusion was: This is true: once you refer to a form object or its property (frmBudget.startupposition = 3) - the object is then loaded until you unload it or reset your project (State loss - At this point ALL variables are reset and any values lost)
This comment was minimized by the moderator on the site
Hello jfjoyner3,How are you. As you can see in the two screenshots, I changed the "record sheet" into "sheet2" in the VBA Code. 
Then I returned to Excel workbook. After I made some changes in the sheet1, all these changes are recorded in the sheet2.
As for the continuous-calculation issue, could you please send us the screenshots or video of your problem? So we can fully understand what is going on here. Thanks! 
This comment was minimized by the moderator on the site
MandyZhou, thank you. Does this macro start automatically when I open the spreadsheet? Or must I start it manually? 
The continuous calculation problem is related to another app. 
This comment was minimized by the moderator on the site
Thank you very much!
This comment was minimized by the moderator on the site
Hello jfjoyner3,You are welcome. After you save the spreadsheet with the macro VBA code, the macro will start automatically every time you open the spreadsheet. No need to start it manually. Any question, please feel free to contact us. Have a nice day!Best regards,Mandy   
This comment was minimized by the moderator on the site
I tried using this VBA code in my excel sheet. But it gave me errors. I don't know from where this macro should be called and what is the argument to the function you have provided when it is called.
This comment was minimized by the moderator on the site
Great work. Unfortunately, there are some issues with your code. - It will add a comment even on the first entry of the cell. How can I make it track changes from the second entry not the first one? - Once I enter a value in a cell I can't do "Undo". - It doesn't work with tables. Try to use on a table then try to add or delete a raw and the code will crash. I really wish I have the knowledge to get the code to work the way I want it as described above.
This comment was minimized by the moderator on the site
I have the same issue. "Undo" and "Redo" buttons don't work anymore. Is there any solution for this?
There are no comments posted here yet
Leave your comments
Posting as Guest
Rate this post:
0   Characters
Suggested Locations