Create a log sheet
There are event type codes that can do all those.
Workbook_Open..........to stamp the date/time of opening.
Workbook_Open..........to stamp login name and computer name.
BeforeSave and BeforeClose to stamp date/time of saving and/or closing.
SheetChange.........to stamp date/time a particular cell or cells have been
changed.
How extensive do you want the stamping and where?
Do you want a running accumulation or just last user?
Some example code to be placed in Thisworkbook.
Private Sub Workbook_Open()
Set rng1 = Worksheets("Login").Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0)
With rng1
.Value = Environ("Username")
.Offset(0, 1).Value = Format(Now, "dd/mm/yyyy hh:mm:ss")
.Offset(0, 2).Value = NameOfComputer()
End With
End Sub
Code to be placed in a General module.
Needed to get computer name.
Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) _
As Long
Public Function NameOfComputer()
' Returns the name of the computer
Dim ComputerName As String
Dim ComputerNameLen As Long
Dim Result As Long
ComputerNameLen = 256
ComputerName = Space(ComputerNameLen)
Result = GetComputerName(ComputerName, ComputerNameLen)
If Result < 0 Then
NameOfComputer = Left(ComputerName, ComputerNameLen)
Else
NameOfComputer = "Unknown"
End If
End Function
Gord Dibben MS Excel MVP
On Tue, 13 Apr 2010 10:35:01 -0700, terilad
wrote:
Hi,
I am looking to create a worksheet within my workbook to log open, save and
data entry events, with time and date and user and computer, is there a macro
that can do this?
Many thanks
Mark
|