Hi
I wrote a macro for this (see below):
---
- saves entries into a log file (e.g. log.xls. This file has to exist
as empty file prior to invoking this macro)
- could save additional fields as well
- adapt path name, file name, etc according to your situation
- put the macro in your workbook module 8not in a standard module)
And as additional comment: You mentioned that the user should not
recognize this kind of tracking:
- I don't know in which country you live but if you would do this in
Germany (as employer) you'll definetly get sued for this (especially if
you have a staff delegate).
- In Germany you have to inform the employees about this tracking and
in most cases your're NOT allowed to track on user base but just on an
aggregated level
- so be careful using tracking mechanism
-----------
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim log_wbk As Workbook
Dim log_wks As Worksheet
Dim last_log_row As Long
Dim path As String
Dim log_filename As String
Dim source_wbk As Workbook
Dim source_wks As Worksheet
'Initialization
Application.ScreenUpdating = False
path = "C:\Temp\"
log_filename = "log.xls"
Set source_wbk = ActiveWorkbook
'check if logging workbook is open / if not open it
On Error Resume Next
Set log_wbk = Workbooks(log_filename)
On Error GoTo 0
If log_wbk Is Nothing Then
Workbooks.Open filename:=path & log_filename
Set log_wbk = Workbooks(log_filename)
End If
Set log_wks = log_wbk.Worksheets("Tabelle1") 'change this
'get last used row in the logging workbook
last_log_row = log_wks.Cells(Rows.Count, "A").End(xlUp).row
'log data - change to your needs
With log_wks
.Cells(last_log_row + 1, 1).Value = Application.UserName
.Cells(last_log_row + 1, 2).Value = _
Format(Now, "MM/DD/YYYY hh:mm:ss")
'save some cell values as audit trail
Set source_wks = source_wbk.Worksheets("Tabelle1")
.Cells(last_log_row + 1, 3).Value = source_wks.Range("A1").Value
.Cells(last_log_row + 1, 4).Value = source_wks.Range("A2").Value
End With
' save the changes
Application.DisplayAlerts = True
log_wbk.Save
log_wbk.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
