Hi
We have a local intranet and some of the Excel files are shared to many users.
In a MS Excel sheet, i want to incorporate the following :
a) Login name/user name of the person and the date/time of opening the file.
b) The details should appear one by one in subsequent rows for each user
c) The details should be overwritten it is the same user opens the file later
d) The details can appear from the first row.... before the beginning of the
content of the letter

The format will look some how like this: Login Name
Date of last opening of file
A
01/01/08 11.00 AM
B
05/05/08 10.00 AM
C
07/07/08 09.00 AM

Looking for a solution..
Thanks in advance

Hi,

Alt+F11 to open VB editor. Double click 'This Workbook' and paste this in on
the right. Note it uses a sheet called "Records" which you must manually
create.

Private Sub Workbook_Open()
Sheets("Records").Select
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set myrange = Range("B1:B" & lastrow)
For Each c In myrange
If c.Value = Environ("Username") Then
c.Offset(, -1).Value = Now
ActiveWorkbook.Save
Exit Sub
End If
Next
Cells(lastrow + 1, 1).Value = Now
Cells(lastrow + 1, 2).Value = Environ("Username")
ActiveWorkbook.Save
End Sub

Mike

"saran" wrote:

Hi
We have a local intranet and some of the Excel files are shared to many users.
In a MS Excel sheet, i want to incorporate the following :
a) Login name/user name of the person and the date/time of opening the file.
b) The details should appear one by one in subsequent rows for each user
c) The details should be overwritten it is the same user opens the file later
d) The details can appear from the first row.... before the beginning of the
content of the letter

The format will look some how like this: Login Name
Date of last opening of file
A
01/01/08 11.00 AM
B
05/05/08 10.00 AM
C
07/07/08 09.00 AM

Looking for a solution..
Thanks in advance

Hi Mike...
Thank u very much. It is working..
But it is showing only 3 users at any time. It is vomiting/not showing some
of the users in random.
What could be the problem?

"Mike H" wrote:

Hi,

Alt+F11 to open VB editor. Double click 'This Workbook' and paste this in on
the right. Note it uses a sheet called "Records" which you must manually
create.

Private Sub Workbook_Open()
Sheets("Records").Select
lastrow = Cells(Cells.Rows.Count, "B").End(xlUp).Row
Set myrange = Range("B1:B" & lastrow)
For Each c In myrange
If c.Value = Environ("Username") Then
c.Offset(, -1).Value = Now
ActiveWorkbook.Save
Exit Sub
End If
Next
Cells(lastrow + 1, 1).Value = Now
Cells(lastrow + 1, 2).Value = Environ("Username")
ActiveWorkbook.Save
End Sub

Mike

"saran" wrote:

Hi
We have a local intranet and some of the Excel files are shared to many users.
In a MS Excel sheet, i want to incorporate the following :
a) Login name/user name of the person and the date/time of opening the file.
b) The details should appear one by one in subsequent rows for each user
c) The details should be overwritten it is the same user opens the file later
d) The details can appear from the first row.... before the beginning of the
content of the letter

The format will look some how like this: Login Name
Date of last opening of file
A
01/01/08 11.00 AM
B
05/05/08 10.00 AM
C
07/07/08 09.00 AM

Looking for a solution..
Thanks in advance

Put the following macro in the workbook code area:

Private Sub Workbook_Open()
Sheets("log").Activate
If IsEmpty(Range("A1").Value) Then
n = 1
Else
n = Cells(Rows.Count, "A").End(xlUp).Row + 1
End If

If Cells(n - 1, "B").Value = Environ("username") Then Exit Sub

Cells(n, 2).Value = Environ("username")
Cells(n, 1) = Now()
ActiveWorkbook.Save
End Sub

You need a tab named "log". Because it is workbook code, it is very easy to
install and use:

1. right-click the tiny Excel icon just to the left of File on the Menu Bar
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm



--
Gary''s Student - gsnu200800


"saran" wrote:

Hi
We have a local intranet and some of the Excel files are shared to many users.
In a MS Excel sheet, i want to incorporate the following :
a) Login name/user name of the person and the date/time of opening the file.
b) The details should appear one by one in subsequent rows for each user
c) The details should be overwritten it is the same user opens the file later
d) The details can appear from the first row.... before the beginning of the
content of the letter

The format will look some how like this: Login Name
Date of last opening of file
A
01/01/08 11.00 AM
B
05/05/08 10.00 AM
C
07/07/08 09.00 AM

Looking for a solution..
Thanks in advance