I'm currently trying to set up a spread sheet which will allow me to keep a track of each time someone in the office changes any information within it, I have got this to work via the following code:-
Dim PreviousValue
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value <> PreviousValue Then
Sheets("log").Cells(65000, 1).End(xlUp).Offset(1, 0).Value = _
Application.UserName & " changed cell " & Target.Address _
& " from " & PreviousValue & " to " & Target.Value
End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PreviousValue = Target.Value
End Sub
Which logs any changes to all the cells in the workbook. However I also have this code:-
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Sheets("MEP 01").Range("D5").Value = Date
Sheets("MEP 01").Range("E5").Value = Time
End Sub
This then logs the last time the document was saved, I would like to know if there is any way I can remove cells D5 and E5 from the audit code, as these two cells along with D4 (which contains =TODAY() formula) will change often and will make my audit trail rather large.
Any help would be most appreciated.
In your Workbook_BeforeSave add:
Application.EnableEvents = False
before changing the sheets but be sure to add
Application.EnableEvents = True
before ending the Sub.
This prevents the Worksheet_Change event from being triggered so nothing gets written to your log.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments