View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.misc
terilad terilad is offline
external usenet poster
 
Posts: 141
Default Create a log sheet

Hi,

would be good to have a running accumulation on sheet named log, with
sampling of data per sheet name with the number added or taken off and the
initials entered. These are all data that is entered onto these stock
sheets, also would be be good to record events such as macros that are run on
the workbook.

Many thanks

Mark

"Gord Dibben" wrote:

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


.