View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Gord Dibben Gord Dibben is offline
external usenet poster
 
Posts: 22,906
Default 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