Have a look at the link I included above. The macro there does what you
need. I think you may have misunderstood me (or I wasn't clear). The macro
I referred you to will track changes to all cells in the workbook, but it
only logs changes made to one cell at a time. In other words, if you were to
copy/paste a range or more than one cell, it would not log that change.
Actually, it looks like I made a few minor changes (for instance, adding the
worksheet name to the log), so try the version below. I haven't played with
this for a while, but it seems to still work. This code is from
www.ozgrid.com (see link in my other posting), with only minor changes by me.
Note that this macro puts the log into "Sheet1" and hides it, so make sure
your "Sheet1" is available to be overwritten, or update the references to
Sheet1 below with another sheet. You can unhide it with
Format->Sheet->Unhide.
Also once you have it working, try copying and pasting. You may find the
resetting of the Copymode irritating, but it should be tolerable. You may
find the original version preferable since it doesn't have this issue.
[You'll need to add back in the logging of the sheetname.] My version shows
the value with formatting of the new cell, but only the unformatted value of
the old cell.
Put the following code in the Thisworkbook module:
-------------------------
Option Explicit
Dim vOldVal 'Must be at top of module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With
Call LogChanges(vOldVal, Target, Sh)
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
End With
End Sub
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
vOldVal = Target
End Sub
------------------------------------
Put the following code in a new module (I call mine ChangeLog).
--------------------------------------
Option Explicit
Sub LogChanges(ByVal vOldVal, ByVal Target As Range, ByVal Sh As Object)
Dim bHasFormula As Boolean
If Target.Cells.count > 1 Then Exit Sub
'On Error Resume Next
If IsEmpty(vOldVal) Then vOldVal = "[Empty Cell]"
bHasFormula = Target.HasFormula
With Sheet1
.Unprotect Password:="Secret"
If .Range("A1") = vbNullString Then
.Range("A1:H1") = Array("#", "CELL CHANGED", "NUMCELLS",
"OLD VALUE", _
"NEW VALUE", "TIME OF CHANGE", "DATE OF CHANGE",
"USERID")
End If
With .Cells(.Rows.count, 1).End(xlUp)(2, 1)
.Formula = "=Row()-1"
.Offset(0, 1) = Target.Worksheet.Name & "!" & Target.Address
.Offset(0, 2) = Target.Cells.count
.Offset(0, 3) = vOldVal
With .Offset(0, 4)
If bHasFormula = True Then
.Formula = "'" & .Formula
Target.Copy
.PasteSpecial (xlPasteFormats)
Else
Target.Copy
.PasteSpecial (xlPasteAll)
End If
End With
.Offset(0, 5) = Time
.Offset(0, 6) = Date
.Offset(0, 7) = Application.UserName
End With
.Cells.Columns.AutoFit
.Protect Password:="Secret"
.Visible = xlSheetHidden
End With
vOldVal = vbNullString
On Error GoTo 0
End Sub
----------------------------------
Gav123 said:
I've managed to find this solution, which tracks multiple cell changes. I'm
not too worried about formatting or formula changes..
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("$A$1:$AA$1000")) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
With Worksheets("Sheet2")
.Select
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = Target.Address
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Target.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Now()
ActiveCell.NumberFormat = "dd/mm/yy"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Application.UserName
Worksheets("sheet1").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End With
End If
End Sub
What I would like to do now is log the sheet name too as there is multiple
named sheets within the workbook, any ideas?
Thanks in advance,
Gav.
bstobart said:
I've spent some time on this topic recently and now have a solution with
which I'm satisfied. [I'll plan to share it once I have it cleaned up.]
Before I wrote mine, I came across several other solutions. The one at the
link below is pretty good. Its limitations are
(1) it handles changes to only one cell, if the user changes multiple cells
at once, it does not log that change and
(2) it does not track formula or formatting changes, only changes to the
"value" field.
But I believe it addresses your stated need.
http://www.ozgrid.com/VBA/track-changes.htm
Gav123 said:
Hi All,
I'm looking for a way to track changes to worksheets without using sharing
the workbook.
The information I would like to track is..
Users (Say last 20)
Date and Time
What has been changed (Added or Deleted)
Is this possible?
Any help or advice would be appreciated,
Thanks in advance,
Gav.
