Method of providing Full Audit of a Worksheet
In some cases, usually where someone is using a Workbook as a simple
database it is necessary to provide tracking of each cell that is changed.
To do this you need to create two additional sheets, one that we will
call ‘AuditSheet’ with headings similar to the
following;
|
And a second sheet that is an exact copy of the sheet that holds you base
data.
In the Worksheet_Change event of the Worksheet
that contains your base data place the following code.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim strUName As String
Dim varOV As
Variant
Dim varNV As
Variant
Dim strAddr As
String
strUName
= Application.UserName
strAddr
= Target.Address
varOV
= Sheets("Sheet2").Range(strAddr).Value
varNV
= Sheets("Sheet1").Range(strAddr).Value
Sheets("AuditSheet").Range("NextAuditEntry").Value = strUName
Sheets("AuditSheet").Range("NextAuditEntry").Offset(0,
1).Value = strAddr
Sheets("AuditSheet").Range("NextAuditEntry").Offset(0,
2).Value = Now()
Sheets("AuditSheet").Range("NextAuditEntry").Offset(0,
3).Value = varOV
Sheets("AuditSheet").Range("NextAuditEntry").Offset(0,
4).Value = varNV
ThisWorkbook.Names("NextAuditEntry").RefersTo = Sheets("AuditSheet").Range("NextAuditEntry").Offset(1, 0)
Sheets("Sheet2").Range(strAddr).Value = varNV
End Sub
Each time a
value is changed in a cell on Sheet1, the change is recorded in the ‘AuditSheet’ and the new value is also written in the ‘copy’
sheet so that if a further change is made, or an ‘Undo’ is performed it will
also be recorded.