Can we enable logging in Excel such that every action is logged on Excel
load event.
Hi questioneer,
Yes, logging is possible, but you have to program it.
Follow these staps:
1) open the workbook where loggnig must be appied
2) Switch to VBA with Ctrl-F11
3) Hit Ctrl-R to make the projects visible
3) right-click in you project
4) Select Insert -> Module
5) Add this code
' Begin of code ------------------------
Option Explicit
Public lngLogFile As Long
' End of code ------------------------
6) Dubble click on "ThisWorkbook"
7) Add this code
' Begin of code ------------------------
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Local Error GoTo Error_Workbook_BeforeClose
Write #lngLogFile, Format(Now, "YYYYMMDDHHNNSS") & _
", Closed "
Close lngLogFile
Error_Workbook_BeforeClose:
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Write #lngLogFile, Format(Now, "YYYYMMDDHHNNSS") & _
", Saved "
End Sub
Private Sub Workbook_Open()
Dim strFileName As String
lngLogFile = FreeFile
strFileName = ThisWorkbook.FullName
strFileName = Left(strFileName, Len(strFileName) - 3)
strFileName = strFileName & _
Format(Now, "YYYYMMDDHHNNSS") & ".log"
Open strFileName For Output As lngLogFile
Write #lngLogFile, "Username: " & Environ("USERNAME")
Write #lngLogFile, "Computer: " & Environ("COMPUTERNAME")
Write #lngLogFile, Format(Now, "YYYYMMDDHHNNSS") & _
", Started"
Write #lngLogFile, Format(Now, "YYYYMMDDHHNNSS") & _
", Sheet: " & ActiveSheet.Name
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Write #lngLogFile, Format(Now, "YYYYMMDDHHNNSS") & _
", Activated: " & Sh.Name
End Sub
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Write #lngLogFile, Format(Now, "YYYYMMDDHHNNSS") & _
", Calculated: " & Sh.Name
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Write #lngLogFile, Format(Now, "YYYYMMDDHHNNSS") & _
", SheetsChange: " & Sh.Name
Write #lngLogFile, Format(Now, "YYYYMMDDHHNNSS") & _
", Range: " & Target.AddressLocal
End Sub
' End of Code --------------------
8) for each Sheet:
8a) Dubble click on the sheet
8b) Add this code
' Begin of code ------------------------
Private Sub Worksheet_Calculate()
Write #lngLogFile, Format(Now, "YYYYMMDDHHNNSS") & _
", Calculated"
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Write #lngLogFile, Format(Now, "YYYYMMDDHHNNSS") & _
", Change of " & Target.AddressLocal
Write #lngLogFile, Format(Now, "YYYYMMDDHHNNSS") & _
", into " & CStr(Target.Value)
End Sub
' End of code ------------------------
You can experiment with some other events on each sheet until you get
all the info in the log you want to have.
Hoop This Helps
Executor
