Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Sheet, Hide Sheet | Excel Discussion (Misc queries) | |||
create a formula in one sheet that would read data from separate sheet automatically | Excel Discussion (Misc queries) | |||
How to create a formala in one sheet for a cell in another sheet? | Excel Worksheet Functions | |||
Create a sheet name, won't let me, says already sheet named that | Excel Worksheet Functions | |||
How do I create a command button to jump from sheet to sheet in a. | Excel Worksheet Functions |