ExcelBanter - Excel application logs

On 10 mrt, 05:39, "msnews.microsoft.com"
wrote:
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