A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Create a log sheet



 
 
Thread Tools Display Modes
  #1  
Old April 13th 10, 06:35 PM posted to microsoft.public.excel.misc
terilad
external usenet poster
 
Posts: 141
Default Create a log sheet

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
Ads
  #2  
Old April 13th 10, 07:43 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,912
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


  #3  
Old April 13th 10, 08:50 PM posted to microsoft.public.excel.misc
terilad
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

>
> .
>

  #4  
Old April 13th 10, 11:18 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,912
Default Create a log sheet

I don't understand the part about sampling of data per sheet name with the
number added or taken off.

Please provide more details on what you visualize.


Gord

On Tue, 13 Apr 2010 12:50:12 -0700, terilad
> wrote:

>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


  #5  
Old April 14th 10, 03:54 AM posted to microsoft.public.excel.misc
JB
external usenet poster
 
Posts: 115
Default Create a log sheet

http://boisgontierjacques.free.fr/fi...eModifiees.xls

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Sh.Name <> "Espion" Then
Application.EnableEvents = False
temp = Application.CountA(Sheets("espion").Range("a:a")) + 1
Sheets("espion").Cells(temp, 1) = Sh.Name
Sheets("espion").Cells(temp, 2) = Target.Address
Sheets("espion").Cells(temp, 3) = Now
Sheets("espion").Cells(temp, 4) = [mémo]
Sheets("espion").Cells(temp, 5) = Target
Sheets("espion").Cells(temp, 6) = Environ("username")
Application.EnableEvents = True
End If
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
If Target.Count = 1 Then
ActiveWorkbook.Names.Add Name:="mémo", RefersToR1C1:="=" & Chr(34)
& Target.Value & Chr(34)
End If
End Sub

JB
http://boisgontierjacques.free.fr

On 13 avr, 19:35, 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


  #6  
Old April 15th 10, 08:47 PM posted to microsoft.public.excel.misc
terilad
external usenet poster
 
Posts: 141
Default Create a log sheet

Hi Gord,

I have 100 sheets all with names of stock items, syringes, needles etc, in
these sheets I have 4 columns Date, Amount of stock in or out, Balance of
stock and persons initials, what I am looking to is log the entries that
appear in these sheets on one log sheet with date and time, pc name and user
name.

The colums in the stock sheets are A, B, C, D

Regards


Mark

"Gord Dibben" wrote:

> I don't understand the part about sampling of data per sheet name with the
> number added or taken off.
>
> Please provide more details on what you visualize.
>
>
> Gord
>
> On Tue, 13 Apr 2010 12:50:12 -0700, terilad
> > wrote:
>
> >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

>
> .
>

  #7  
Old April 15th 10, 10:43 PM posted to microsoft.public.excel.misc
Gord Dibben
external usenet poster
 
Posts: 22,912
Default Create a log sheet

It can be done using Workbook_SheetChange code but much more detail is
required.

How do these cells on your 100 sheets become populated/changed?

Manual entries one cell/sheet at a time?

You have 4 columns of data...............how many cells in each column?

Which column(s) or cell(s) change would you want to trigger the code?

Would you want the triggering cell's address also logged to logsheet?

If you want you can send me a workbook with a clearer explanation of your
wishes.

email to gorddibbATshawDOTca change the obvious.


Gord

On Thu, 15 Apr 2010 12:47:02 -0700, terilad
> wrote:

>Hi Gord,
>
>I have 100 sheets all with names of stock items, syringes, needles etc, in
>these sheets I have 4 columns Date, Amount of stock in or out, Balance of
>stock and persons initials, what I am looking to is log the entries that
>appear in these sheets on one log sheet with date and time, pc name and user
>name.
>
>The colums in the stock sheets are A, B, C, D
>
>Regards
>
>
>Mark
>
>"Gord Dibben" wrote:
>
>> I don't understand the part about sampling of data per sheet name with the
>> number added or taken off.
>>
>> Please provide more details on what you visualize.
>>
>>
>> Gord
>>
>> On Tue, 13 Apr 2010 12:50:12 -0700, terilad
>> > wrote:
>>
>> >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

>>
>> .
>>


  #8  
Old April 16th 10, 10:16 PM posted to microsoft.public.excel.misc
terilad
external usenet poster
 
Posts: 141
Default Create a log sheet

Thanks Gord, will email a workbook to you.

Regards


Mark

"Gord Dibben" wrote:

> It can be done using Workbook_SheetChange code but much more detail is
> required.
>
> How do these cells on your 100 sheets become populated/changed?
>
> Manual entries one cell/sheet at a time?
>
> You have 4 columns of data...............how many cells in each column?
>
> Which column(s) or cell(s) change would you want to trigger the code?
>
> Would you want the triggering cell's address also logged to logsheet?
>
> If you want you can send me a workbook with a clearer explanation of your
> wishes.
>
> email to gorddibbATshawDOTca change the obvious.
>
>
> Gord
>
> On Thu, 15 Apr 2010 12:47:02 -0700, terilad
> > wrote:
>
> >Hi Gord,
> >
> >I have 100 sheets all with names of stock items, syringes, needles etc, in
> >these sheets I have 4 columns Date, Amount of stock in or out, Balance of
> >stock and persons initials, what I am looking to is log the entries that
> >appear in these sheets on one log sheet with date and time, pc name and user
> >name.
> >
> >The colums in the stock sheets are A, B, C, D
> >
> >Regards
> >
> >
> >Mark
> >
> >"Gord Dibben" wrote:
> >
> >> I don't understand the part about sampling of data per sheet name with the
> >> number added or taken off.
> >>
> >> Please provide more details on what you visualize.
> >>
> >>
> >> Gord
> >>
> >> On Tue, 13 Apr 2010 12:50:12 -0700, terilad
> >> > wrote:
> >>
> >> >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
> >>
> >> .
> >>

>
> .
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create Sheet, Hide Sheet Dave Excel Discussion (Misc queries) 2 October 30th 07 03:40 PM
create a formula in one sheet that would read data from separate sheet automatically QD Excel Discussion (Misc queries) 0 December 8th 06 04:17 AM
How to create a formala in one sheet for a cell in another sheet? Beth Excel Worksheet Functions 1 October 20th 05 02:14 PM
Create a sheet name, won't let me, says already sheet named that Buckwheat Excel Worksheet Functions 7 May 2nd 05 12:55 AM
How do I create a command button to jump from sheet to sheet in a. Darlenew Excel Worksheet Functions 3 March 22nd 05 10:36 PM


All times are GMT +1. The time now is 04:51 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright ©2004-2014 ExcelBanter.
The comments are property of their posters.