How to track changes without sharing workbook?
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.
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 Application.Undo 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 .AddComment Else 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
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:
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 months agoI 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.
- To post as a guest, your comment is unpublished.· 3 years agoGreat 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.
- To post as a guest, your comment is unpublished.· 2 years agoI have the same issue. "Undo" and "Redo" buttons don't work anymore. Is there any solution for this?