Hello Master,
I have got a macro to track changes made in excel sheet. But the changes are
tracked in sheet1 of the same work book. I want a macro which keeps the
track of changes, in a different workbook and not the same workbook. In
short instead of keeping the log of changes in sheet1 of the same workbook,
I want to keep the log of changes in a separate workbook. As & when I make
sum changes in workbook X, it should be tracked in a different workbook Y.
Or else if someone could help me do it in a different way. When a file is
shared among different users, the track of changes made can be obtained by
clicking on track changes option in excel. It gives an option to display all
the changes in a separate sheet named history. A macro that can save this
history sheet in a separate workbook might help to solve the problem.
Hope to get some help on this. I work on a shared file where many people do
unauthorised changes due to which all people working on the file have to
suffer. The problem is, once a file is unshared we loose the history of
track changes.
Plsssssssssss help.
Regards,
Seema
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe, reply using "remove me" as the subject.
Dim vOldVal 'Must be at top of module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim bBold As Boolean
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
bBold = Target.HasFormula
With Sheet1
.Unprotect Password:="Secret"
If .Range("A1") = vbNullString Then
.Range("A1:F1") = Array("CELL CHANGED", "OLD VALUE", _
"NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE", "USER")
End If
With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
.Value = Target.Address
.Offset(0, 1) = vOldVal
With .Offset(0, 2)
If bBold = True Then
.ClearComments
.AddComment.Text Text:="OzGrid.com:" & Chr(10) & "" &
Chr(10) & _
"Bold values are the results of formulas"
End If
.Value = Target
.Font.Bold = bBold
End With
.Offset(0, 3) = Time
.Offset(0, 4) = Date
.Offset(0, 5) = Environ("Username")
End With
.Cells.Columns.AutoFit
.Protect Password:="Secret"
End With
vOldVal = vbNullString
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
On Error GoTo 0
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As
Range)
vOldVal = Target
End Sub
