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.
Track changes without sharing workbook with VBA code
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
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:
Best Office Productivity Tools
Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel
Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...
Supports Office/Excel 2007-2021 & newer, including 365 | Available in 44 languages | Enjoy a full-featured 30-day free trial.
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!
